When restoring a database to a different server in SQL Server, one common problem is matching up logins and users. The login information is stored in the master database, while the user information is stored in the specific database you are working with. This can cause issues because the login information is tied to a specific Security Identifier (SID), which may not match on the new server even if the login names are the same.

One simple solution would be to restore the database, drop all of the users, and recreate them. However, this can be time-consuming and problematic if there are hundreds of users or if the user owns objects that cannot be dropped.

Fortunately, SQL Server offers several commands to help you relink the logins and users. Here are the different commands and how they can be used:

Standard Logins

If a standard login already exists, you can use the sp_change_users_login command to relink the login and the user.

sp_change_users_login @Action='Report'

This stored procedure has three options:

• Auto_Fix: Links users and logins that have the same name.
• Report: Shows a list of users in the current database that are not linked to a login.
• Update_One: Allows you to link a user and login that may not have the same exact name.

Removing Users

If there are users in the database that do not have a corresponding login on the server and you no longer need these users, you can use the sp_revokedbaccess command to remove them from the database and clean up the user list.

sp_revokedbaccess 'NorthDomain\Mike'

Windows Logins

If your Windows NT login and user names do not match, you can use the following commands to link them:

Note: Microsoft does not recommend updating system tables directly, so you should only use this if you fully understand what you are doing and how to recover if there is a problem. You will also need to change the server setting to allow updates to system tables.

DECLARE @sysxlogins_sid VARBINARY(85)
SELECT @sysxlogins_sid = sid FROM master.dbo.sysxlogins WHERE name = 'NorthDomain\Joe'
UPDATE sysusers SET sid = @sysxlogins_sid WHERE name = 'Joe'

Logins

To find out what access a login has on your server, you can use the sp_helplogins command.

sp_helplogins

This will display a list of databases the login has access to, as well as other information about the login such as the SID.

If there is a user in your database that does not have a corresponding login on the server, you can use the following commands to create the new login:

Standard Login

sp_addlogin

Windows Authentication Login

sp_grantlogin 'NorthDomain\Mary'

After creating the login, you may need to link the appropriate database user to this new login using the steps mentioned above.

Set Default Database

If you need to change the default database for a login, you can use the sp_defaultdb command for both Windows and standard logins.

sp_defaultdb 'NorthDomain\Mary', 'master'

Recreating Logins

In some cases, you may need to duplicate the entire login list from one server to another. This can be achieved manually by running the above commands, or you can use the stored procedure developed by Microsoft. When creating the logins manually, the original SID is not kept, and a new SID is created as the login is being created. Therefore, you will need to relink the logins and users for your database.

For more information on how to transfer logins and passwords between instances of SQL Server, you can refer to the official Microsoft documentation.

By using these commands and techniques, you can easily relink logins and users in SQL Server, ensuring that your database is properly restored and accessible on a different server.