Orphaned Windows Logins on SQL Server
Recently I’ve had to restore a SQL 2012 backup from a production machine to a test machine. Unfortunately as part of that restore process, logins to the database where not created.
As these users where “Orphaned” i.e. existed in the database, but not in the server, users where not able to login. These can be created manually, however is slow and time consuming where there are multiple users.
The following SQL (from Ted Krueger’s article) automatically created all the logins. The code works in SQL 2005, 2008, 2008 R2 and 2012. It may work in 2014 and 2016, however this is not tested.
SET NOCOUNT ON USE AdventureWorks GO DECLARE @loop INT DECLARE @USER sysname DECLARE @sqlcmd NVARCHAR(500) = '' IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL BEGIN DROP TABLE #orphaned END CREATE TABLE #Orphaned (UserName sysname,IDENT INT IDENTITY(1,1)) INSERT INTO #Orphaned (UserName) SELECT [name] FROM sys.database_principals WHERE [type] IN ('U','S') AND is_fixed_role = 0 AND [Name] NOT IN ('dbo','guest','sys','INFORMATION_SCHEMA') IF(SELECT COUNT(*) FROM #Orphaned) > 0 BEGIN SET @loop = 1 WHILE @loop < = (SELECT MAX(IDENT) FROM #Orphaned) BEGIN SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop) IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0 BEGIN IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER') BEGIN SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] FROM WINDOWS' Exec(@sqlcmd) PRINT @sqlcmd END IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER') BEGIN SET @sqlcmd = 'CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''password''' Exec(@sqlcmd) PRINT @sqlcmd END END SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']' Exec(@sqlcmd) PRINT @USER + ' link to DB user reset'; SET @loop = @loop + 1 END END SET NOCOUNT OFF