37

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sanjeev40084
  • 9,227
  • 18
  • 67
  • 99
  • possible duplicate of [How do you test for the existence of a user in SQL Server?](http://stackoverflow.com/questions/356000/how-do-you-test-for-the-existence-of-a-user-in-sql-server) – IMSoP Nov 04 '13 at 02:04

2 Answers2

73

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.

doug_w
  • 1,330
  • 10
  • 10
  • what is the N in here N'username' ? – zer0w1dthspace Feb 15 '11 at 10:18
  • 3
    The 'n' is just telling sql server that the string value is unicode. This may prevent problems for people working in various character sets. – doug_w Mar 16 '11 at 09:04
  • 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. – Brad Sep 07 '21 at 15:13
18

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.

Community
  • 1
  • 1
QMaster
  • 3,743
  • 3
  • 43
  • 56
  • 2
    Older answers are not incorrect, but this is the cleanest and most concise method with 2016+. – grasmi Mar 13 '20 at 11:02
  • ```DROP USER IF EXISTS 'DOMAIN\user-name'``` returns "Incorrect syntax - expecting ID or QUOTED_ID" .. How to resolve this please? – Dieter Sep 19 '22 at 14:19
  • DROP USER IF EXISTS [DOMAIN\user-name] use [ ] instead of ' ' also ALTER LOGIN [DOMAIN\user-name] DISABLE – Abuelhija Jan 27 '23 at 06:47