Find orphaned users?
In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) or accounts that aren't linked (an account may have been deleted at the server level but not db level, then re added but the db level was never cleaned up). I've got a very messy server and it would be awesome if there was a query to run to find these.
The following script to find sql server orphaned users from the Brent Ozar Unlimited site iterates through all databases and lists the orphaned users by database, along with the drop command to remove them. There may be a neater/newer way of handling this but this appears to function correctly on 2005-2012.
DECLARE @SQL nvarchar(2000) DECLARE @name nvarchar(128) DECLARE @database_id int SET NOCOUNT ON; IF NOT EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '%#orphan_users%') BEGIN CREATE TABLE #orphan_users ( database_name nvarchar(128) NOT NULL, [user_name] nvarchar(128) NOT NULL, drop_command_text nvarchar(200) NOT NULL ) END CREATE TABLE #databases ( database_id int NOT NULL , database_name nvarchar(128) NOT NULL , processed bit NOT NULL ) INSERT #databases ( database_id , database_name , processed ) SELECT database_id , name , 0 FROM master.sys.databases
WHERE name NOT IN ('master' , 'tempdb' , 'msdb' , 'distribution' , 'model') WHILE (SELECT COUNT(processed) FROM #databases WHERE processed = 0) > 0 BEGIN SELECT TOP 1 @name = database_name, @database_id = database_id FROM #databases WHERE processed = 0 ORDER BY database_id SELECT @SQL = 'USE [' + @name + ']; INSERT INTO #orphan_users (database_name, user_name, drop_command_text) SELECT DB_NAME() , u.name , ' + '''' + 'USE [' + @name + ']; ' + 'DROP USER [' + '''' + ' + u.name + ' + '''' + '] ' + '''' + ' FROM master..syslogins l RIGHT JOIN sysusers u ON l.sid = u.sid WHERE l.sid IS NULL AND issqlrole <> 1 AND isapprole <> 1 AND ( u.name <> ' + '''' + 'INFORMATION_SCHEMA' + '''' + ' AND u.name <> ' + '''' + 'guest' + '''' + ' AND u.name <> ' + '''' + 'dbo' + '''' + ' AND u.name <> ' + '''' + 'sys' + '''' + ' AND u.name <> ' + '''' + 'system_function_schema' + '''' + ')' PRINT @SQL; EXEC sys.sp_executesql @SQL UPDATE #databases SET processed = 1 WHERE database_id = @database_id; END SELECT database_name , [user_name] , drop_command_text FROM #orphan_users ORDER BY [database_name] , [user_name]; DROP TABLE #databases; DROP TABLE #orphan_users; SET NOCOUNT OFF;