Often, in development, we will take a backup of a database from one SQL Server to another. Perhaps we are moving a database to our development environment to do some local debugging.
Invariably, if you use a backup/restore process, you have an orphaned database "user" account that you need to map to a login. I have done this several times, but I keep forgetting the command, so I thought I would blog it so that I won't forget it, and perhaps others would benefit from this information.
Assume your database that you restored has a user, called "appUser", that is used by some application that accesses that databasde. Use the following steps to match a login account to the database user.
- Create a login account, if there is not one, in this case create a SQL Server login "appLogin" (In this case we are creating a database login account, not a Windows account. But it is just as easy to do the same thing with a Windows account).
- Execute the following command:
EXEC
sp_change_users_login 'Update_One', 'appUser', 'appLogin';
GO
That's it. Pretty easy. Make sure to check the login and user to ensure that this login/user can access the database.
You can check the details out at the Microsoft SQL Server Books-On-Line for sp_change_users_login.