Monday, March 28, 2011

Restoring an MSSQL DB but user can't log in (SQL Server authentication)

Very common and typical problem when using SQL server auth mode (instead of windows auth mode).

In MSSQL you have a server user entity that is mapped to a specific DB user entity.
When you restore a DB for the first time, you need to create an MSSQL user and map it to your DB's user.
When you restore a DB in the following times (not the first time), you just need to re-map the MSSQL user to the DB user.

Creating an MSSQL user is pretty straight forward - just go to the security node of the server (not of the DB) and create a user with the same name that you have in the DB.

Mapping/Re-Mapping the user can be done using this script (for example we will use the db "mydb" and user "myuser"):

USE mydb
EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';
EXEC sp_changedbowner 'myuser'

No comments:

Post a Comment

Feel free to comment. No links/URLs allowed in comments.