USP_FixOrphans - Fix Orphanned Users Procedure (for ALL databases)

Script to fix orphanned users after a restore operation.
This builds on USP_FixUsers and works for all databases.

Save it to master database or common tools db.

Usage : EXEC Usp_FixOrphans 'databasename' or EXEC Usp_FixOrphans '/all'

ALTER PROCEDURE USP_FixOrphans(@chvparameterdbname VARCHAR(255))
AS
/*


Fix Orphanned Users


Stored Procedure


Procedure to reattach users after restores



1
27/05/2007
sqlsolace
Original Version



*/
SET NOCOUNT ON

-- check for parameters
IF @chvparameterdbname IS NULL
OR LTRIM(RTRIM(@chvparameterdbname)) = ''
BEGIN
PRINT 'USP_FixOrphans : Parameter missing : must provide a database name or /all'

RETURN
END

SET @chvparameterdbname = LTRIM(RTRIM(@chvparameterdbname))

-- check for reconnecting all dbs
IF @chvparameterdbname = '/all'
BEGIN
PRINT 'USP_FixOrphans : Executing for all databases'

SET @chvparameterdbname = ''
END
ELSE
BEGIN
PRINT 'USP_FixOrphans : Executing for ' + @chvparameterdbname
END

IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
BEGIN
DROP TABLE ##SERVERLOGINS
END

IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
BEGIN
DROP TABLE ##DBUSERS
END

IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
BEGIN
DROP TABLE ##DBLIST
END

IF OBJECT_ID('tempdb..##DBResults') IS NOT NULL
BEGIN
DROP TABLE ##DBRESULTS
END

DECLARE  @DBName VARCHAR(128);

DECLARE  @SQLCmd VARCHAR(2000);

DECLARE  @NumberOfDBs INT;

DECLARE  @chvOrphanName VARCHAR(255);

DECLARE  @chvCurrentDatabaseName VARCHAR(255);

-- Get the SQL Server logins
-- Create login table
CREATE TABLE ##SERVERLOGINS (
[SID]        VARBINARY(85)   NULL,
[LOGIN_NAME] VARCHAR(128)   NULL);

-- Populate login table
INSERT INTO ##SERVERLOGINS
SELECT SID,
CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
FROM   MASTER.DBO.SYSLOGINS;

-- Create list of databases
CREATE TABLE ##DBLIST (
[DBNAME] VARCHAR(128))

IF @chvparameterdbname = ''
BEGIN
-- perform for all dbs on server
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    DBID > 4
ORDER BY NAME;
END
ELSE
BEGIN
INSERT INTO ##DBLIST
SELECT   NAME
FROM     MASTER..SYSDATABASES
WHERE    NAME = @chvparameterdbname
ORDER BY NAME;
END

SELECT @NumberOfDBs = COUNT(* )
FROM   ##DBLIST

-- Create the output table for the Database User ID's
CREATE TABLE ##DBUSERS (
[DATABASE_USER_ID] VARCHAR(128),
[SERVER_LOGIN]     VARCHAR(128),
[DATABASE_ROLE]    VARCHAR(128),
[DATABASE_NAME]    VARCHAR(128));

CREATE TABLE ##DBRESULTS (
[DATABASE_NAME]   VARCHAR(128),
[TOTAL_USERS]     SMALLINT,
[USERS_OK]        SMALLINT,
[USERS_ORPHANNED] SMALLINT,
[USERS_FIXED]     SMALLINT,
[USERS_NOLOGIN]   SMALLINT);

-- Declare a cursor to loop through all the databases on the server
DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
INSERT INTO ##DBRESULTS
(DATABASE_NAME,
TOTAL_USERS,
USERS_OK,
USERS_ORPHANNED)
SELECT ##DBLIST.DBNAME,
ALLCOUNT,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS ALLCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
GROUP BY DATABASE_NAME) ALLUSERS
ON ALLUSERS.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME

--select * from ##DBResults

-- cursor through users we can repair
DECLARE CURREPAIRORPHANEDUSERS CURSOR FORWARD_ONLY FOR
SELECT DATABASE_NAME,
DATABASE_USER_ID
FROM   ##DBUSERS
INNER JOIN ##SERVERLOGINS
ON ##DBUSERS.DATABASE_USER_ID = ##SERVERLOGINS.LOGIN_NAME
WHERE  SERVER_LOGIN = '* Orphaned *'

OPEN CURREPAIRORPHANEDUSERS

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName

WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN
IF @chvOrphanName = 'dbo'
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGEDBOWNER ''Sa'''
END
ELSE
BEGIN
SET @SQLCmd = 'USE [' + @chvCurrentDatabaseName + '] EXEC SP_CHANGE_USERS_LOGIN ''update_one'',' + '''' + @chvOrphanName + ''',' + '''' + @chvOrphanName + ''''
END
END

PRINT @SQLCmd

EXEC( @SQLCmd)

FETCH NEXT FROM CURREPAIRORPHANEDUSERS
INTO @chvCurrentDatabaseName,
@chvOrphanName
END

CLOSE CURREPAIRORPHANEDUSERS

DEALLOCATE CURREPAIRORPHANEDUSERS

-- repopulate the ##DBUsers table so can compare what the script has achieved
DELETE ##DBUSERS

DECLARE CSRDB CURSOR  FOR
SELECT   DBNAME
FROM     ##DBLIST
ORDER BY DBNAME

-- Open the cursor and get the first database name
OPEN CSRDB

FETCH NEXT FROM CSRDB
INTO @DBName

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN

-- populate ##DBUsers table for database list
SELECT @SQLCmd = 'INSERT ##DBUsers ' + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' + ' FROM [' + @DBName + '].[dbo].[sysusers] su' + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' + ' ON sm.groupuid = sug.uid)' + ' ON su.uid = sm.memberuid ' + ' WHERE su.hasdbaccess = 1' + ' AND su.[name] != ''dbo'' '

--print @SQLCmd
EXEC( @SQLCmd)

-- Get the next database name
FETCH NEXT FROM CSRDB
INTO @DBName

-- End of the cursor loop
END

-- Close and deallocate the CURSOR
CLOSE CSRDB

DEALLOCATE CSRDB

-- database name and total users
UPDATE ##DBRESULTS
SET    USERS_FIXED = NEWRESULTS.OKCOUNT - USERS_OK,
USERS_NOLOGIN = NEWRESULTS.ORPHANNEDCOUNT
FROM   ##DBRESULTS
INNER JOIN (SELECT ##DBLIST.DBNAME AS DBNAME,
OKCOUNT,
ORPHANNEDCOUNT
FROM   ##DBLIST
INNER JOIN (SELECT   COUNT(* )     AS OKCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN <> '* Orphaned *'
GROUP BY DATABASE_NAME) OK
ON OK.DATABASE_NAME = ##DBLIST.DBNAME
INNER JOIN (SELECT   COUNT(* )     AS ORPHANNEDCOUNT,
DATABASE_NAME
FROM     ##DBUSERS
WHERE    SERVER_LOGIN = '* Orphaned *'
GROUP BY DATABASE_NAME) ORPHANNED
ON ORPHANNED.DATABASE_NAME = ##DBLIST.DBNAME) NEWRESULTS
ON ##DBRESULTS.DATABASE_NAME = NEWRESULTS.DBNAME

SELECT DATABASE_NAME,
TOTAL_USERS,
USERS_OK        AS INITIAL_USERS_OK,
USERS_ORPHANNED AS INITIAL_USERS_ORPHANNED,
USERS_FIXED,
USERS_NOLOGIN   AS USERS_NO_MATCHING_LOGIN
FROM   ##DBRESULTS

GO