Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  


Restore Mapping from Sql Server User to Login
Each SQL Server database has a collection of Users (Server/Database/mydb/Security/Users folder).
Each SQL Server instance has a collection of Logins (Server/Security/Logins).

In order to log in to sql, a user must be mapped to a login. Typically, you would create the user by going to 'logins' and under 'user mapping' tab, click 'Map' checkboxes and create a user in the databases the login should have access to.

However, if you need to add a mapping, but the user already exists from some previous action, this will fail! SQL Server maps users to logins by an internal key (not the textual name) so a user and login may have the same name, but not be mapped here... meaning the user will not be able to login, even though there is both a login and user specified.

To fix this scenario, use the system stored procedure sp_change_users_login on the database which has the user that needs a login. Pass the 'report' parameter to find a list of users which appear to have broken mappings:

sp_change_users_login 'report'

Assuming your user name is returned, you can go ahead and use the 'update_one' parameter to fix the mapping. (Otherwise go back and make sure both the user and login exist in the database).

sp_change_users_login 'update_one', 'myusername', 'myloginname'
(myusername can be the same as myloginname)

Created By: amos 8/20/2010 1:52:17 PM

 > amos 8/25/2010 5:47:03 PM
If you are still having trouble, delete the user from the database using right click, delete. Or drop the entire login, but this will delete all occurances of the user from all databases!