Archive

Uncategorized

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
(DatabaseName VARCHAR(128))

INSERT INTO @DBList
( DatabaseName )
SELECT name
FROM master.sys.databases
WHERE name <> ‘master’

WHILE (SELECT COUNT(*) FROM @DBList) > 0
BEGIN
SELECT TOP 1 @Databasename = Databasename
FROM @DBList

SELECT @Command = ‘USE [‘ + @Databasename + ‘] IF EXISTS (SELECT * FROM ‘ + @Databasename + ‘.sys.database_principals WHERE name = N’
+ ”” + @UserName + ”” + ‘) DROP USER [‘+ @UserName +’]’

PRINT @Command
EXECUTE (@Command )

DELETE FROM @DBList WHERE DAtabaseName = @Databasename
END