SQL Server restorig backup on Developement (Or Test) Server and user mapping

Question

I got 3 server.

1. development (my local development machine)
2. Test purpose (Beta site).
3. Production

I got Instances in both SQL server 2005 and 2008.

So, When I want updated data, i take the latest backup from our production server and restore it on the Test or development Server. I got a script that make the changes (restore the file and make some slight changes on the databases.)

My problem is, each time i do this, The SQL server Login (created on the server) I use lose it's Database user mapping. I got to delete it form the restored database and recreate it from the root Security Logins.

Is there a way to include that in my restore script?

Answer 1

I got to delete it form the restored database and recreate it from the root Security Logins.

No, you don't need to do this - at least not on SQL Server 2005 and 2008.

What you need to do (like in a script) is this:

USE (your database)
GO

ALTER USER YourUserNo1 WITH LOGIN = YourServerLoginNo1

That associates your user YourUserNo1 in your database (whatever) to the server login called YourServerLoginNo1.

Works pretty nice'n'easy, I'd say! You can even do this as a last step in the restore script (which I'm sure you have to restore those things over and over again)