cannot execute as the database principal because the principal does not exist - DB Restoration

Question

I have received one database backup from one of my client and now I'm trying to restore it into my system.

I tried to search this issue on google and tried most of the suggested solutions but none of them is working. Can anyone please suggest some specific solution for this error after restoration of database?

Screenshot of the Error

Thanks in advance!

Answer 1

When you restore a database it contains database users. These users are transported with the database, whenever you perform a backup and a restore to a different SQL Server.

On your source and target SQL Servers you will have SQL Server Logins. These logins are either SQL Server accounts (Native SQL Server logins) or they will be Windows Server/Domain accounts (Windows Accounts).

SQL Server Logins

SQL Server Logins (Native SQL Logins or Windows Accounts) are stored in the master database and have a unique SID assigned to each SQL Login. You can retrieve a list of SQL Server logins when you query the system catalog view sys.server_principals.

select * from sys.server_principals

You will receive a list of SQL Server logins for your SQL Server instance.

Note: These are the SQL Server Logins and not the database users.

Database Users

Database Users can be queried by querying the system catalog view sys.database_principles of each user database by issuing the following query:

USE [<your_db>]
GO
SELECT * FROM sys.database_principals

You will receive a list of database users that have database privileges assigned to them.

Note: These are the Database Users and not SQL Server Logins.

Normal Behaviour

When you create a database and assign a "user" to the database with certain privileges you are in fact doing the following (partly in the background):

1. Creating SQL Server Login with password (sys.server_principal)
2. Creating a database user in the database (sys.database_principal)
3. Granting permissions to the database user (in various tables, depending on the privileges)

In this case the SID of the SQL Login (sys.server_principal) will match the SID of the database user (sys.database_principal).

Restore Behaviour

When you restore a database from a source SQL Server the database users (Native SQL Server Logins and local Windows Accounts only) in the source database will have different SIDs than the SQL Server Logins on the target server. This is because the SID is unique for the source and target Native SQL Server Login or Local Windows Account.

The SQL Server Logins that are based on Windows Domain Accounts will always have the same SID, because SQL Server will retrieve these values from Active Directory.

When you restore the database from the source to the target SQL Server the SIDs of the Native SQL Server Logins will be mismatched, even though a user might be listed in the sys.server_principals system management catalog of the SQL Server instance and in the sys.database_principals system management catalog of the restored database.

Solution

To rectify this and allow you to navigate the "SQL Server Login | permissions" and/or the "Database Properties | Permissions" you can relink these orphaned database users to the SQL Server Login.

Switch to your user database and query the orphaned database users:

USE [your_db]
GO
sp_change_users_login 'Report'

If a user is reported as orphaned you can relink the SQL Server Login with the Database User by issuing the following command:

USE [your_db]
GO
sp_change_users_login 'Update_one', '<database_user>', '<sql_server_login>'

This will relink the (Native) SQL Server Login (sys.server_principal) on your target instance, with the Database User (sys.database_principal) of the restored database.

Alternative

Seeing as sp_change_users_login is deprecated, you could achieve the same with the ALTER USER statement:

USE [<your_db>]
GO
ALTER USER <database_user> 
    WITH LOGIN = <sql_server_login>

In some cases

...when you receive a database backup from a client, you might not have a corresponding SQL Server Login to link to the Database User. In that case you can create a SQL Server Login without assigning permissions to the restored database and then link the newly create SQL Server Login with the Database User with the above mentioned statements.

Answer 2

The error you are getting derives from code that the SSMS dialog(on your picture) launches.

When you ask server about the effective permissions of some user via that dialog, server tryies to impersonate the selected user, i.e. when you click on test_user, effective permissions the code that it launches is this one:

EXECUTE AS USER = N'test_user';
SELECT 
    permission_name AS [Permission]
FROM fn_my_permissions(N'[yourDB]', N'DATABASE')
ORDER BY permission_name;
REVERT;

This is in case the user (test_user) is created without login.

In case the user has a corresponding login, server tries to impersonate that corresponding login because it may correspond to more users in case of login = win group.

There are other cases when user is created from certificate, for example, but I cannot monitor now what code is executed in this case.

To find out what code is executed by server in your case just use SQL Server Profiler, you'll se what it tries to inpersonate and find the cause of the error.

I know how to receive this error in particular case of Windows group: as it cannot be impersonated, you get exactly that error.

Other possible causes: you may lack the permission to impersonate that user. Are you executing the code as sysadmin? If not, this can be your case

UPDATE

I was able to reproduce it just now. Orphan users that was ported with your restored database to another server will produce the same error if you try to impersonate them.

If there is an existing login with the same name for your orphan user (but with different sid) you can fix this by executing the following code:

alter user test_user with login = test_user

If you have no corresponding login you can recreate it with the same sid, or just create a new login and update the existing user like I did above.

If you don't need this user at all just drop it