There are many scripts in blogs and forums to remove a user from all databases in a SQL Server instance. I want to keep as simple as possible and just drop the database user account. This script will error on the databases where the users owns a schema. I want to know those databases and want to retain the login. Plus having heard so many warnings about cursors I don’t use them. Some would say a loop in a WHILE statement is the same thing as a cursor.
Simply put, get a list of databases to check for the user, loop through each, and drop the user if it exists.
DECLARE @Databasename VARCHAR(128)
, @Command VARCHAR(255)
, @UserName VARCHAR(128)
SELECT @UserName = ” — Enter a User
DECLARE @DBList TABLE
INSERT INTO @DBList
( DatabaseName )
WHERE name <> ‘master’
WHILE (SELECT COUNT(*) FROM @DBList) > 0
SELECT TOP 1 @Databasename = Databasename
SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’
EXECUTE (@Command )
DELETE FROM @DBList WHERE DAtabaseName = @Databasename