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.

SQL Server 2008 Release

Microsoft creates products faster then people are making babies in highly populated countries. Half of the SQL Server users have not upgraded to SQL Server 2005 and now microsoft is releasing new version SQL Server 2008. I guess releasing a new version is the best excuse of not fixing problems in current version. I have not read whats new in 2008 but i am sure there will be alot to impress my directors and purchase officers to spend money on it.

I dont know honestly, its my first blog. If you dont know which version of SQL Server you are using, type this command in Query Analyzer

SQL Command : SELECT @@VERSION