Script to De-Orphan SQL Logins

–By Lori Brown   @SQLSupahStah

I am working with a client who is migrating from SQL 2012 to SQL 2016. It just so happens that while they have just a few databases to move, they also have a ton of SQL logins that needed to be migrated. This is not an issue since I know to use sp_help_revlogin to script logins and passwords into a script that can be applied to the new SQL 2016. However, inevitably, there are SQL logins that have been deleted from SQL but not from the database or while testing on SQL 2016 the databases are refreshed and now logins are orphaned. I needed to have a script that would look at each database and give me a script that could be used to create or de-orphan these logins.

I figured that with a little Google-fu I would find someone who had already created a way to do this. And, while I did find a solution, I decided to give it my own twist. My solution is to have a script that I can run when needed that will output the statements that I need to create or de-orphan logins from all databases in an instance. I combined things from these two blogs:

http://www.sqlshack.com/creating-a-stored-procedure-to-fix-orphaned-database-users/

https://www.codeproject.com/articles/489617/create-a-cursor-using-dynamic-sql-query

Here is my code and a quick look at the output:

USE master

SET NOCOUNT ON

DECLARE @usercount INT

DECLARE @sqlstr1 NVARCHAR(1000)

DECLARE @sqlstr2 NVARCHAR(500)

DECLARE @dbname VARCHAR(128)

DECLARE @username SYSNAME

DECLARE @OrphUsers AS CURSOR

DECLARE db CURSOR FOR

SELECT name FROM sys.databases

WHERE name NOT IN (‘master’,‘tempdb’, ‘msdb’, ‘model’)

AND is_read_only = 0 AND state = 0

OPEN db

FETCH NEXT FROM db INTO @dbname

WHILE @@FETCH_STATUS=0

BEGIN

SET @sqlstr1 = ‘SET @orphanedusers = CURSOR

       FOR SELECT a.NAME

       FROM [‘+@dbname+‘].sys.database_principals a

       LEFT OUTER JOIN sys.server_principals b

             ON a.sid = b.sid

       WHERE (b.sid IS NULL)

            AND (a.principal_id > 4)

             AND (a.type_desc = ”SQL_USER”)

       OPEN @orphanedusers’

EXEC sys.sp_executesql @sqlstr1, N’@orphanedusers cursor output’, @OrphUsers OUTPUT

FETCH NEXT FROM @OrphUsers INTO @username

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @usercount = COUNT(*) FROM sys.server_principals WHERE name = @username

IF @usercount = 0

BEGIN

PRINT ‘–User ‘+@username+‘ does not exist and must be created. Please use a strong password.’

SET @sqlstr2 = ‘USE [master] CREATE LOGIN ‘ + @username + ‘ WITH PASSWORD = ”ABC123”’

END

             ELSE

             BEGIN

                    PRINT ‘–User ‘+@username+‘ is an orphan.’

SET @sqlstr2 = ‘USE [‘+@dbname+‘] ALTER USER ‘ + @username + ‘ WITH LOGIN = ‘ + @username

END

PRINT (@sqlstr2)

FETCH NEXT FROM @OrphUsers INTO @username

END

CLOSE @OrphUsers

DEALLOCATE @OrphUsers

FETCH NEXT FROM db INTO @dbname

END

CLOSE db

DEALLOCATE db

SET NOCOUNT OFF

The output labels what needs to be done to make sure the users have access to their databases. If the user does not exist in SQL at all then a CREATE LOGIN statement is generated. If the user is simply orphaned an ALTER LOGIN statement is generated.

The only thing I did not do yet (mostly because my needs were centered around SQL logins) is to include Windows domain accounts in my logic. As soon as I add that to my script, I will repost. Or, if someone out there adds it, please send an update.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!