Friday 21 August 2009

Restoring SQL Server databases to a different machine

SQL Server is extremely flexible and allows databases to be backed up and restored very easily and independently of hardware requirements.

There is, however, one nagging problem when restoring a database to a different machine(say copying a development database to a live environment) and that is SQL Servers security system.

SQL Server has logins against the instance and then users against each database - so each database has security independent of all the other databases unless logins have been granted access to any server roles.

When the database restore process completes there might be users listed on the database who do not have logins on that instance or even if the user has a login the guids might not match.

So for SQL Server 2000 and above firstly identify what the problem is by running :

exec sp_change_users_login @Action='Report'


Then for each UserName identified you can either match existing logins to database user using :
exec sp_change_users_login 'Auto_Fix', @UserNamePattern ='username'

(replacing username with the UserName from the first query).

To save having to check which to do manually it does no harm to run that command for users that don't exist yet - just run the first command again to identify who is still not sorted and then run the following to create logins for each remaining user :
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'


Just make sure you are connected as an admin user and you are connected to the correct database before running the commands.

It's also worth pointing out that this isn't news - I was passed most of this info when I started the role and have modified as I've gone along and there are plenty of postings out there with the same /similar information.