Are you experiencing this error with newly restored database?
Cannot execute as the database principal because the principal "abc" does not exist, this type of principal cannot be impersonated, or you do not have permission.
SQL Server throws this error when a user tries to execute a code but SQL Server cannot find the login account. This can happen when a database from an instance (let's say Server-01) is restored to a different instance (Server-02) of SQL Server because it breaks the link between user accounts and login accounts. Example, assume that Server-01 has a database called Sales, and this has a login called Jane and user called Jane in Sales database, linked with Jane Login. If the database is restored to Server-02, you will see above-mentioned error because;
- Jane Login is exist in the Server-02 but it has a different SID that does not match with SID of user Jane in Sales database.
- Jane Login does not exist.
If the reason is the #1, then you can re-map it or if the reason is #2, then it needs to be created and re-mapped.
Here is an example. This example shows one way of seeing the error and the way of fixing it. The following code;
- Creates a database named Sales.
- Creates a login called Jane and add Jane as a user to Sales database.
- Configure appropriate permissions to Jane for reading data.
- Take a backup of the Sales database.
- Delete the Sales database.
- Delete the Jane Login.
- Restore the Sales database.
- Check and see whether Jane can access the database.
- Create Jane's login again and try.
- Find out orphaned users.
- Map Jane's user with the newly created login.
USE master; GO -- create the database CREATE DATABASE Sales GO -- create the login CREATE LOGIN Jane WITH PASSWORD = 'Pa$$w0rd'; -- connect with Sales and add Jane as a user USE Sales; GO -- create user CREATE USER Jane FOR LOGIN Jane; -- give permission for reading. EXEC sp_addrolemember 'db_datareader', 'Jane'; -- change the security context and execute a code -- Jane can read records EXECUTE AS USER = 'Jane'; SELECT USER_NAME() SELECT * FROM sys.objects; REVERT; -- Connect to master database USE master; GO -- take a backup and delete the database and login BACKUP DATABASE Sales TO DISK = N'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\Sales.bak' WITH NOFORMAT, NOINIT DROP DATABASE Sales; DROP LOGIN Jane; -- restored the database again. Note that -- this has the user but instance does not have the login RESTORE DATABASE Sales FROM DISK = N'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\Sales.bak'; -- Connect with Sales USE Sales; GO -- This throws the error EXECUTE AS USER = 'Jane'; SELECT USER_NAME() SELECT * FROM sys.objects; REVERT; -- Connect with master and re-create the login USE master; GO CREATE LOGIN Jane WITH PASSWORD = 'Pa$$w0rd'; -- Connect again and check USE Sales; GO -- This throws the error again EXECUTE AS USER = 'Jane'; -- Checking orphaned users -- This will show that Jane's user account is orphaned. SELECT d.* FROM sys.database_principals d LEFT OUTER JOIN sys.server_principals s ON d.sid = s.sid WHERE s.sid IS NULL and d.type = 'S'; -- Map the Jane's user account with her login ALTER USER Jane WITH LOGIN = Jane;
