If you used a SQL login and needed to restore a database (dev/test), you should notice that your SQL login doesn’t work for the database anymore. The reason for this is that SQL login and database user mapping was lost. Even you can see the user name in database user’s mapping, however in internal SQL memory the restored database’s user has different reference than the SQL login.
There are several ways to solve this.
- Simply delete user mapping from the database using GUI, and create a new mapping for the selected SQL login.
- Use sp_change_users_login stored procedure which is deprecated and will be removed in future SQL server versions.
[code lang=”sql”]
EXEC sp_change_users_login ‘Update_One’, YourUser, YourUser
[/code] - Use following ALTER USER. Code snippet:
[code lang=”sql”]
ALTER USER YourUser WITH LOGIN = YourUser
[/code]