SQL Server - "Managing database users and logins"

In Microsoft SQL Server there are two forms of “User ID's”:

1. There is a SQL Server “Login” which aloows the user to connect to the SQL Server. This data is usually found under the “Security” folder.
2. Under the Databases –> <DBNAME> –> Security –> Users folder are the users that are allowed to access the database in question.

Question
What happens when you are restoring a foreign database into your SQL Server. The Database has a set of users allowed to access it but there is no corresponding SQL Server “Login”?

Answer
This is where sp_change_users_login comes into play. There are two main ways to use the stored procedure:

• The following code block shows you how to automatically create a “Login” for the “User” in your recently restored database.

use <DBNAME>
go
exec sp_change_users_login 'Auto_Fix', '<USER>',NULL,'<PASSWORD>'
go

• The following code block associates the “User” in the database to an existing “Login”.

use <DBNAME>
go
exec sp_change_users_login 'Update_one', '<USER>','<LOGIN>'
go