How to transfer a database AND its users from one SQL Server to another?

Question

we have one db in an SQLServer instance that should be transferred to another instance (let's say dbname="testdb" with a user testuser).

This transfer works easily when we use SQL Server Management Studio (backup the database on the source machine and restore it on the target machine).

The problem is now that I can't connect with the testuser on the target machine. It's part of the db but not part of the SQL Server wide logins.

Now my question. How can I add the user from the db to the SQL Server logins?

Thanks in advance for any comments!

Cheers, Helmut

Answer 1

What really helped me in the end was running the following SQL script (after restore)

USE testdb;
GO
EXEC sp_change_users_login 'Auto_Fix', 'testuser', NULL, 'testpwd';
GO

This "connects" the database user "testuser" to the server login "testuser". If no server login "testuser" exists a new one is created.

For documentation please see http://msdn.microsoft.com/en-us/library/ms174378.aspx