ISO TSQL to Delete User IF EXISTS from DB only (not SQL Login)

Question

Post reply

ISO TSQL to Delete User IF EXISTS from DB only (not SQL Login)

Express12

SSCoach

Points: 18514
More actions
November 8, 2007 at 6:27 am

#119142

In Search Of... T-SQL to DELETE a User from a database ONLY IF the User Exists. I do NOT want to DELETE the LOGIN.
For example:
User myDomain\BOB resides in Security Logins
User myDomain\BOB resides in a User of Database CUSTOMER
Looking for the IF EXISTS syntax, then DROP myDomain\BOB out of the DB User.
BT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

query sys.sysusers for the user name

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Express12 SSCoach Points: 18514 More actions · Answer 2

OK - but I cannot delete from system catalogs. This T-SQL:

USE myDatabase

GO

IF EXISTS (SELECT Name FROM sys.sysusers WHERE Name = 'myDomain\BOB')

DELETE FROM sys.sysusers WHERE Name = 'myDomain\BOB'

GO

Renders this Error Msg:

Msg 259, Level 16, State 1, Line 3

Ad hoc updates to system catalogs are not allowed.

Anyone have T-SQL to accomplish this via T-SQL? (I need to imbed this in a script !!)

BT

Express12 SSCoach Points: 18514 More actions · Answer 3

Got it:

USE myDatabase

GO

IF EXISTS (SELECT Name FROM sys.sysusers WHERE Name = 'myDomain\BOB ')

DROP User [myDomain\BOB ]

GO

CREATE USER [myDomain\BOB ] FOR LOGIN [myDomain\BOB ]

GRANT EXECUTE ON sp_MyProc TO [myDomain\BOB ]

BT

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

That's the way to do it. For regular operations (add, edit, delete) or objects, you don't want to mess with the system tables as a rule. There are exceptions, but for the most part there are commands to work with the objects.

AnthonyNilsson Newbie Points: 1 More actions · Answer 5

Use [DB_Name]

IF OBJECT_ID('Tempdb..#Tmp') IS NOT NULL DROP TABLE #Tmp

select name into #Tmp from sys.database_principals where type <> 'R' and name not in ('dbo','public','guest','sys','INFORMATION_SCHEMA')

except

select name from sys.server_principals

SELECT 'IF EXISTS (SELECT [name] from sys.database_principals where [name] = '''+NAME+''') BEGIN DROP USER ['+NAME+'] END;'

from #Tmp