Fix Database Users Logins in SQL Server

Every time a DBA restores the database, they need to fix logins in SQL Server. I refresh databases using a job which runs step by step. All i have to do is enable the job and create a schedule for the time i want refresh to take place.

Step 1.
Backup the production database.

Step 2.
Copy the database backup from production server to development or QA.

Step 3.
Kill all the connections in SQL Server. I use this Kill SQL Connection script.

Step 4.
Restore the database on development or QA SQL Server.

Step 5.
Fix Logins, remap them other wise users will not be able to login.

If its a development box then there are few more steps. I have to script out the new objects to make sure i am not going to overwrite any development work including sql server stored procedures and view etc.

Here is the script i use.


USE DATABASE_NAME
GO
DECLARE
@User_Name varchar(255),
@Adhoc_SQL nvarchar(2000)
DECLARE User_Cursor CURSOR FOR
select
sysusers.[name]
from
sysusers
JOIN
master..syslogins sl
ON
sysusers.[name] = sl.[name]
where
sysusers.issqluser = 1
and
(
sysusers.sid is not null
and
sysusers.sid <> 0x0
)
and
suser_sname(sysusers.sid) is null
AND
sysusers.[name] <> 'dbo'

OPEN User_Cursor
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Adhoc_SQL = 'EXEC sp_change_users_login '+
'@Action = ''Auto_Fix'', ' +
'@UserNamePattern = ''' + @User_Name + ''''
print @Adhoc_SQL
EXEC sp_ExecuteSQL @Adhoc_SQL
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
END
CLOSE User_Cursor
DEALLOCATE User_Cursor

Make sure you replace the database name in the script. You can also run the above SQL Script in Query Analyzer of SQL Server.