Saturday, 18 July 2009

TSQL : Remove a user from all databases

Drop User from all databases on a server -
EXEC sp_Msforeachdb "USE [?]; IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'USERNAME') DROP USER [USERNAME]"
GO


Drop the login -
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'USERNAME') DROP LOGIN [USERNAME]
GO

2 comments:

DonOmite said...

Missing a '?' in there. Like USE ? Otherwise it won't look anywhere but at the current db.

r5d4 said...

thank you, good spot.