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.



SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Error:
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Reason:
 The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Solution:
Let us assume that user was trying to delete user which is named as ‘mtohamy’ and it exists in the database ‘TWO’.
Now run following script with the context of the database where user belongs.
USE TWO;SELECT s.nameFROM sys.schemas sWHERE s.principal_id USER_ID('mtohamy');
In my query I get following two schema as a result.




Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
Now if you drop the database owner it will not throw any error.
Here is generic script for resolving the error:
SELECT s.nameFROM sys.schemas sWHERE s.principal_id USER_ID('mtohamy');
Now replace the result name in following script:
ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;