November 8, 2007 at 6:27 am
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.
November 8, 2007 at 6:32 am
query sys.sysusers for the user name
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 8, 2007 at 6:44 am
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 !!)
November 8, 2007 at 7:02 am
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 ]
November 8, 2007 at 8:24 am
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.
June 19, 2023 at 7:30 am
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy