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

I use the SQL Compare product from Red Gate (http://www.red-gate.com/products/SQL_Compare/index.htm). There are other similar products around but I've had no reason to look for one as SQL Compare has never let me down.

You'll find it is useful for a lot more than the your current requirement as it will help synchronize all types of database object, not just login and permissions.

Answer 2

The easiest way to do this is with Microsoft's sp_help _revlogin (had to put a space in there to fool the formatting), 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