Cannot execute as the database principal because the principal does not exist - Mismatched Security IDs

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;

1. 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.
2. 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.

view plainprint?

1. USE master;  
2. GO  
3.   
4. -- create the database  
5. CREATE DATABASE Sales  
6. GO  
7.   
8. -- create the login  
9. CREATE LOGIN Jane WITH PASSWORD = 'Pa$$w0rd';  
10.   
11. -- connect with Sales and add Jane as a user  
12. USE Sales;  
13. GO  
14.   
15. -- create user  
16. CREATE USER Jane FOR LOGIN Jane;  
17. -- give permission for reading.  
18. EXEC sp_addrolemember 'db_datareader', 'Jane';  
19.   
20. -- change the security context and execute a code  
21. -- Jane can read records  
22. EXECUTE AS USER = 'Jane';  
23. SELECT USER_NAME()  
24. SELECT * FROM sys.objects;  
25. REVERT;  
26.   
27. -- Connect to master database  
28. USE master;  
29. GO  
30.   
31. -- take a backup and delete the database and login  
32. BACKUP DATABASE Sales TO  DISK = N'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\Sales.bak'   
33. WITH NOFORMAT, NOINIT  
34.   
35. DROP DATABASE Sales;  
36.   
37. DROP LOGIN Jane;  
38.   
39. -- restored the database again. Note that  
40. -- this has the user but instance does not have the login  
41. RESTORE DATABASE Sales FROM DISK = N'D:\MSSQL13.MSSQLSERVER\MSSQL\Backup\Sales.bak';  
42.   
43. -- Connect with Sales  
44. USE Sales;  
45. GO  
46.   
47. -- This throws the error  
48. EXECUTE AS USER = 'Jane';  
49. SELECT USER_NAME()  
50. SELECT * FROM sys.objects;  
51. REVERT;  
52.   
53. -- Connect with master and re-create the login  
54. USE master;  
55. GO  
56. CREATE LOGIN Jane WITH PASSWORD = 'Pa$$w0rd';  
57.   
58. -- Connect again and check  
59. USE Sales;  
60. GO  
61.   
62. -- This throws the error again  
63. EXECUTE AS USER = 'Jane';  
64.   
65. -- Checking orphaned users  
66. -- This will show that Jane's user account is orphaned.  
67. SELECT d.*   
68. FROM sys.database_principals d  
69.  LEFT OUTER JOIN sys.server_principals s  
70.   ON d.sid = s.sid  
71. WHERE s.sid IS NULL and d.type = 'S';  
72.   
73. -- Map the Jane's user account with her login  
74. ALTER USER Jane WITH LOGIN = Jane;  

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;