Restoring a Backup to a different Server - User Permissions

Question

I have backed up and restored a MS SQL Server 2005 database to a new server.

What is the best way of recreating the login, the users, and the user permissions?

On SQL Server 2000's Enterprise Manager I was able to script the logins, script the users and script the user permissions all seperately. I could then run one after the other and the only remaining manual step was to set the login password (which do not script for security reasons)

This does not seem possible in SQL Server 2005's Management Studio, making everything very fiddly and time consuming. (I end up having to script the whole database, delete all logins and users from the new database, run the script, and then trawl through a mixture of error message to see what worked and what didn't.)

Does anyone have any experience and recommendations on this?

Answer 1

The easiest way to do this is with Microsoft's sp_help_revlogin, a stored procedure that scripts all SQL Server logins, defaults and passwords, and keeps the same SIDs.

You can find it in this knowledge base article:

http://support.microsoft.com/kb/918992