sp_change_users_login

This stored procedure has three options Auto_Fix, Report and Update_One. Below is a brief description.

• The Auto_Fix option will link users and logins that have the same name.
• The Report option will show you a list of users in the current database that are not linked to a login.
• The Update_One option 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 and there is not a corresponding login on the server and you do not need this user anymore, you can use this command sp_revokedbaccess to remove the user from the database and cleanup the user list.

sp_revokedbaccess 'NorthDomain\Mike'

Windows Logins
If your NT login and user names do not match you can use the following commands to link your NT login and user information. Microsoft does not recommend updating system tables directly, so you should only use this if you totally 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. In this query we are specifying the login "NorthDomain\Joe" and the database user "Joe", so you will need to update accordingly.

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'

sp_helplogins 'NorthDomain\Mike' 

This will display a list of databases the login has access to as well as other info about the login such as the SID:

Create New Login
If there is a user in your database and there is not a corresponding login on the server you can use the following commands to create the new login.

If you are creating a standard SQL Server login you would use this command sp_addlogin to create the login.

sp_addlogin

If you are creating a Windows authentication login use this command sp_grantlogin.

sp_grantlogin 'NorthDomain\Mary'

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

Set Default Database
In addition if you need to change the default database for a login use this command sp_defaultdb for both Windows or standard logins.

sp_defaultdb 'NorthDomain\Mary', 'master'