SQL Security Align Logins

08/23/21

For security, use this SQL script to Align Logins after migrating a database to a new instance.

USE MASTER

GO

SET NoCount ON

DECLARE @VarDbId INT, @SQL nvarchar(4000), @VDBName nvarchar(260), @OUCounter INT, @Max_OUCounter INT

 

SELECT @VarDbId=4,

@SQL =''

CREATE TABLE #OrphaneUsers

(

ID INT IDENTITY (1,1) NOT NULL,DBName VARCHAR(125) NULL,UserName sysname NULL,UserSID VARBINARY(85) NULL ,LoginExists bit NULL

)

WHILE EXISTS

(SELECT database_id FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE'

)

BEGIN

SELECT TOP 1

@SQL ='Create table #OrphaneUser (UserName sysname null, UserSID varbinary(85) null)

       insert into #OrphaneUser exec ' + '[' + name + ']' + '.dbo.sp_change_users_login ''report''

       insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ '[' + name + ']' + ''' as[dbname], UserName, UserSID,0 from #OrphaneUser

       drop Table #OrphaneUser',

@VDBName=name

FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE' ORDER BY database_id

 

EXEC SP_Executesql @SQL

 

SELECT TOP 1 @VarDbId=database_id FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE'

END

 

UPDATE #OrphaneUsers SET LoginExists = 1 FROM #OrphaneUsers JOIN syslogins ON #OrphaneUsers.UserName=syslogins.NAME

SELECT @OUCounter = 0, @Max_OUCounter =COUNT(0)

FROM #OrphaneUsers WHERE LoginExists = 1

 

WHILE EXISTS

(SELECT TOP 1

id

FROM #OrphaneUsers WHERE LoginExists = 1 AND id > @OUCounter

)

BEGIN

SELECT TOP 1 @OUCounter=id

FROM #OrphaneUsers WHERE LoginExists=1 AND id > @OUCounter

SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Update_One'', '''+UserName+''', '''+UserName+''''

FROM #OrphaneUsers WHERE LoginExists = 1 AND id = @OUCounter

PRINT @SQL

END

 

SELECT ID, DBName, UserName, UserSID, Case LoginExists When  0 Then 'N | Orphaned User' When 1 Then 'Y | See Messages' End as 'LoginExists'  FROM #OrphaneUsers

DROP TABLE #OrphaneUsers

We're available if you need assistance implementing custom SQL code. Contact us for help!

Share This: 

Related Resources

envelope