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
|
|