Cleaning up orphaned users in SQL Server
Short blog post here; Iâ€™m working with my team to clean up some older SQL Servers, and weâ€™re removing several logins that have been dormant or disabled for some time.Â Removing the logins is easy, but it leaves orphaned users in several of our databases.Â I created the script below to cycle through each of the databases on the server and generate a series of DROP USER commands for each database.
DECLARE @t TABLE ( db VARCHAR(100) , u VARCHAR(1000) , s INT ) DECLARE @sql NVARCHAR(4000) SET @sql = 'USE ?; SELECT DB_NAME() , dp.name , s = CASE WHEN s.Schema_id IS NOT NULL THEN 1 ELSE 0 END FROM sys.database_principals AS dp LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid LEFT JOIN sys.schemas s ON s.principal_id = dp.principal_id WHERE dp.type IN ( ''U'', ''S'' ) AND sp.sid IS NULL AND dp.authentication_type IN ( 1, 3 ) AND dp.name <> ''dbo''' INSERT INTO @t EXEC sp_msforeachdb @sql SELECT 'USE ' + db + '; ' + CASE WHEN s = 1 THEN 'ALTER AUTHORIZATION ON SCHEMA::[' + u + '] TO dbo; ' ELSE '' END + ' DROP USER [' + u + ']' FROM @t
If the script discovers an orphaned user, the output will look something like:
USE AdventureWorks2012; DROP USER [olduser]
If that user owns a schema in the database, an ALTER AUTHORIZATION step is added to first transfer the schema to dbo, and then drop the user:
USE AdventureWorks2012; ALTER AUTHORIZATION ON SCHEMA::[olduser] TO dbo; DROP USER [olduser]
Iâ€™m sure there are other ways to accomplish the same thing, but it works well for this task.