Saturday, September 29, 2012

The server principal "sqlLoginName" is not able to access the database "myDatabaseName" under the current security context.

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 1The server principal "mtohamy" is not able to access the database "TWO" 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 <TWO>sp_change_users_login  @Action='Report';

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

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='mtohamy',@LoginName='mtohamy';

This should be done to all the db(s)  that user is associated in.



No comments:

Post a Comment