Can’t Access Database After a Restore

I can’t access my SQL Server database after a restore from a SQL Server authentication account.

This happens when you restore a database from a different server. You need to update the database with the appropriate keys for the users on your server; they are still the ids from the old server.

In the database, you can execute this

EXEC sp_change_users_login ‘Report’
 
to list the users. If you have the user your are looking for, you can run this to fix it.
EXEC sp_change_users_login ‘Auto_Fix’, ‘UserNameHere
 
If you don’t have the user, you can create one and then run the above statement.
 
I didn’t put much detail on this. You might want look in the sp_change_users_login command to get a better understand of what is happening.
Posted in SQL