Drop User from SQL Server Database?

Question

How can I drop user from a database without dropping it's logging?

The script should check if the user exists in database, if does then drop the user.

Solution 1

Is this what you are trying to do??

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'username')
DROP USER [username]

If you are using SQL Server Management Studio you can browse to the user and right-click selecting delete.

Solution 2

The accepted answer is working good enough. Additionally that is good to know SQL Server added IF EXIST to some DROP commands from version 2016 (13.x) including 'DROP USER' command.

IF EXISTS

Applies to: SQL Server ( SQL Server 2016 (13.x) through current version, SQL Database).

Conditionally drops the user only if it already exists.

So you could just delete user as below:

-- Syntax for SQL Server and Azure SQL Database  
DROP USER IF EXISTS user_name  

See the full description in this link: DROP USER (Transact-SQL)

Hope this help.

Comments

[zer0w1dthspace]

what is the N in here N'username' ?

[doug_w]

The 'n' is just telling sql server that the string value is unicode. This may prevent problems for people working in various character sets.

[grasmi]

Older answers are not incorrect, but this is the cleanest and most concise method with 2016+.

[Brad]

Apparently I found this on 9/23/2019 and again today 9/7/2021 (now I know why I dont ever memorize things, I can just google them :) Thanks again for second time, cant upvote you again though.