Automating restores in sql server

Automating fixing of logins,users , orphaned users in sql .

Scenario : -  After database restores from source to destination , most of the time we face issues of orphaned users and logins , following script can be helpful in fixing the issue

  scripts to automate the process

it comprises of 5 steps

1, save logins prior to restore
2, perform restore of database
3, extract   logins info post restoration of database
5, create users if not existing,map users , fix orphaned users (usually SQL logins)

--save logins
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id =  OBJECT_ID(N'tempdb.dbo.prior_restore_dbname') )
DROP TABLE tempdb.dbo.prior_restore_dbname
GO
create table tempdb.dbo.prior_restore_dbname
 (UserName sysname null,
 RoleName sysname null,
 LoginName varchar(80) null,
 DefDBName sysname null,defschemaname sysname null,
 UserID bigint null ,
 SID Bigint null)

 INSERT INTO tempdb.dbo.prior_restore_dbname EXEC SP_HELPUSER

-- Perform restoration of Database

--post restore logins
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.post_restore_dbname') AND type in (N'U'))
DROP TABLE tempdb.dbo.post_restore_dbname
GO
create table tempdb.dbo.post_restore_dbname
 (UserName sysname null,
 RoleName sysname null,
 LoginName varchar(80) null,
 DefDBName sysname null,defschemaname sysname null,
 UserID bigint null ,
 SID Bigint null)

 INSERT INTO tempdb.dbo.post_restore_dbname EXEC SP_HELPUSER

--create users
DECLARE @usercreation VARCHAR(8000)
 if exists               
 (select distinct pr.username 
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null)

begin
     
DECLARE  @login nvarchar(max)
       DECLARE crteuser_Cursor CURSOR LOCAL FOR
               select distinct pr.username --,pr.rolename,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null and pr.rolename<>'public'
              OPEN crteuser_Cursor;
              FETCH NEXT FROM crteuser_Cursor INTO @login
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @usercreation = 'create user ['+@login+'] for login ['+@login+']'
                           exec (@usercreation)
    
                       FETCH NEXT FROM crteuser_Cursor INTO @login
                 END;
              CLOSE crteuser_Cursor;
              DEALLOCATE crteuser_Cursor;
             
              end
             
             
--map users
DECLARE @usermapping VARCHAR(8000)
 if exists               
 (select pr.username ,pr.rolename --,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null)

begin
     
DECLARE  @user nvarchar(100) , @role nvarchar(100)
       DECLARE maplogin_Cursor CURSOR LOCAL FOR
               select pr.username ,pr.rolename --,pr.loginname,po.username,po.rolename,po.loginname
 from tempdb.dbo.prior_restore_dbname pr left join
 tempdb.dbo.post_restore_dbname po on pr.username=po.username where pr.userid>4 and po.username is null and pr.loginname is not null and pr.rolename<>'public'
              OPEN maplogin_Cursor;
              FETCH NEXT FROM maplogin_Cursor INTO @user , @role
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @usermapping = 'EXEC sp_addrolemember '''+@role+''' , '''+@user+''''
                           exec (@usermapping)
    
                       FETCH NEXT FROM maplogin_Cursor INTO @user , @role
                 END;
              CLOSE maplogin_Cursor;
              DEALLOCATE maplogin_Cursor;
             
              end
-- fix orphan users
IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.orphanuser_dbname') AND type in (N'U'))
DROP TABLE tempdb.dbo.orphanuser_dbname
GO
create table tempdb.dbo.orphanuser_dbname
( username  varchar(80) , SID  bigint null)
insert into tempdb.dbo.orphanuser_dbname exec sp_change_users_login 'report'
--orphan users
DECLARE @fixorphan VARCHAR(8000)
 if exists               
 (select orp.username from tempdb.dbo.orphanuser_dbname orp  left join tempdb.dbo.prior_restore_dbname pr  on pr.UserName=orp.username where pr.LoginName is not null)

begin
     
DECLARE  @orphuser nvarchar(max)
       DECLARE fixorphan_Cursor CURSOR LOCAL FOR
               select orp.username from tempdb.dbo.orphanuser_dbname orp  left join tempdb.dbo.prior_restore_dbname pr  on pr.UserName=orp.username where pr.LoginName is not null
              OPEN fixorphan_Cursor;
              FETCH NEXT FROM fixorphan_Cursor INTO @orphuser
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @fixorphan = 'EXEC sp_change_users_login ''update_one'','''+@orphuser+''','''+@orphuser+''''
                           exec (@fixorphan)
    
                       FETCH NEXT FROM fixorphan_Cursor INTO @orphuser
                 END;
              CLOSE fixorphan_Cursor;
              DEALLOCATE fixorphan_Cursor;
             
              end