Description of the issue: When you restore a backup database to another server, you may encounter the error message below when you try to connect.

Msg 916, Level 14, State 1, Line 1
The server principal "sqlLoginName " is not able to access the database "myDatabaseName" under the current security context.


Cause: When you restore a backup database to another server, you may experience a problem with orphaned users. That is to say that the SID system view sysuser is not mapped to a SID syslogins existing.

 
Resolution: To detect orphaned users you can run the following command:
USE <myDatabaseName>
sp_change_users_login  @Action='Report';

You can see that the SID does not match the system views: sys.sysusers and sys.syslogins
USE <myDatabaseName>
SELECT sid FROM sys.sysusers WHERE name = 'sqlLoginName'
SELECT sid FROM sys.syslogins WHERE name = 'sqlLoginName'

To correct this problem of connection between the server connection account specified by the user and <login_name> the database specified by <database_user>, you can run the following command.
USE <myDatabaseName>
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='sqlLoginName',@LoginName=' sqlLoginName '; 


For more information:


Troubleshooting Orphaned Users
http://msdn.microsoft.com/en-us/library/ms175475.aspx

PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/kb/274188/

sp_addlogin (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173768.aspx

 

 

Michel Degremont| Microsoft EMEA
Product Support Services Developer -SQL Server Core Engineer |