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

Question

we have one db in the instance of SQLServer, which should be transferred to another instance (say dbname="testdb" with the user testuser ).

This transfer works easily when we use SQL Server Management Studio (backing up the database on the source computer and restoring it on the target machine).

The problem is that I cannot connect to testuser on the target machine. It is part of db, but not part of SQL Server login.

Now my question. How can I add a user from a database to SQL Server accounts?

Thanks in advance for any comments!

Cheers, Helmut

Answer 1

As a result, it really helped me to run the following SQL script (after recovery)

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

This "connects" the user of the testuser database to the testuser server. If the server "testuser" does not exist, a new one is created.

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

Answer 2

I used to face the same problem. The solution is for you - you need to create a login on the server using the instructions below

Use [testdb] - Your database name

CREATE USER [testuser] FOR LOGIN [testuser]

here your [testuser] is your database user, and the second [testuser] is the name of your server, and with this statement we have juxtaposed.