2010-08-27 (first published: 2010-08-25)
3,351 reads
/* Created By: AJAY DWIVEDI Created Date: DEC 18, 2015 Purpose: Script to Fix Orphan Users for All databases */ SET NOCOUNT ON; DECLARE @ID INT ,@RecordsInserted INT ,@DBName SYSNAME ,@String NVARCHAR(4000) ,@Missing_Logins NVARCHAR(4000) DECLARE @OrphanLoginsTable TABLE (ID INT IDENTITY(1,1) ,DBName SYSNAME NULL ,UserName SYSNAME ,UserSID VARCHAR(2000)) DECLARE @MissingLoginsTable TABLE (ID INT IDENTITY(1,1) ,UserName SYSNAME) DECLARE myCursor CURSOR FOR SELECT name FROM sys.databases WHERE name not in ('master','model','msdb','tempdb') OPEN myCursor FETCH NEXT FROM myCursor INTO @DBName; -- Loop through databases WHILE @@FETCH_STATUS = 0 BEGIN SET @String = ' USE ['+@DBName+'] EXEC sp_change_users_login ''Report''; '; INSERT INTO @OrphanLoginsTable (UserName, UserSID) EXEC sp_executesql @String; SET @RecordsInserted = @@ROWCOUNT; SET @ID = @@IDENTITY; -- Orphans users for which logins already exists IF @RecordsInserted <> 0 BEGIN SELECT 'USE ['+@DBName+']; Exec sp_change_users_login ''auto_fix'','''+UserName+''' ' FROM @OrphanLoginsTable WHERE UserName IN (select name from sys.syslogins); END -- Orphans users corresponding to which No Login exists INSERT INTO @MissingLoginsTable SELECT UserName FROM @OrphanLoginsTable WHERE UserName NOT IN (SELECT p.name FROM sys.database_role_members rm JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id) AND UserName NOT IN ('dbo') AND UserName NOT IN (select name from sys.syslogins) AND UserName NOT IN (SELECT UserName FROM @MissingLoginsTable); DELETE FROM @OrphanLoginsTable; FETCH NEXT FROM myCursor INTO @DBName; END IF (SELECT COUNT(1) FROM @MissingLoginsTable) > 0 BEGIN SELECT @Missing_Logins=COALESCE(@Missing_Logins,'') + (CAST(ID AS VARCHAR(3))+ ') ' + UserName + ' ') FROM @MissingLoginsTable; PRINT ' Below Users do not have corresponding Logins on Instance. Please migrate these Logins first:- '+@Missing_Logins; END CLOSE myCursor DEALLOCATE myCursor