Script to De-Orphan SQL Logins

By Lori Brown | Intermediate

Dec 29

–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

blog_20161229_1

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!

 

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

  • Lisa Dominy says:

    Love this blog, great info! Do you ever receive requests to be contacted to assist customers or answer more questions? Would love to be able to see the comments.

    Lisa Dominy | SQLRx Sales Executive | Integrated Services, Inc. 4144 N. Central Expwy, Suite 430 | Dallas, TX 75204 Office: 214.526.7680 x 111 Toll free: 1-855-85-SQLRX (855-857-7579) Cell: 817-371-2410 Blog: http://blog.sqlrx.com/ Twitter: https://twitter.com/SQLRX Over 30Years of Value and Trust https://www.sqlrx.com/ http://www.isi85.com/

  • >