Table of Contents
SQL Server Error : 15517 Details
SQL Server Error: 15517
Severity: 16
Event Logged or not: No
Description:
Cannot execute as the database principal because the principal “%.*ls” does not exist, this type of principal cannot be impersonated, or you do not have permission.
Severity 16 Description:
Indicates general errors that can be corrected by the user.
Reading sql server error log location from SQL Query
Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go
For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html
Solution for Resolving the Error
We can verify the owner of the database:
SELECT name AS DB, SUSER_SNAME(owner_sid) AS owner FROM sys.databases
If the owner is not sa, you can do ALTER AUTHORIZATION or use the deprecated command sp_changedbowner:
USE [DatabaseName]
GO
EXEC sp_changedbowner 'sa'
GO
Even though it is deprecated, this procedure is still available in later versions and I have had more luck with that command for avoiding putting the database into single user mode.
The following error message can repeatedly appearing in the SQL Server log of servers. It was so common that the logs grew enormously huge and quickly:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.
To alter the database’s owner.
Check sys.databases first:
Query sys.databases with sys.server principals, for example.
SELECT d.name AS 'Database', s.name AS 'Owner' FROM sys.databases d
LEFT JOIN sys.server_principals s ON d.owner_sid = s.sid;
SELECT sp.name AS 'dbo_login', o.name AS 'sysdb_login' FROM Example.sys.database_principals dp
LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid
LEFT JOIN master.sys.databases d ON DB_ID('Example') = d.database_id
LEFT JOIN master.sys.server_principals o ON d.owner_sid = o.sid
WHERE dp.name = 'dbo';
SELECT sp.name AS 'dbo_login', o.name AS 'sysdb_login' FROM Example.sys.database_principals dp LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid LEFT JOIN master.sys.databases d ON DB_ID('Example') = d.database_id LEFT JOIN master.sys.server_principals o ON d.owner_sid = o.sid WHERE dp.name = 'dbo';
Obviously, if you wanted to run it for all DBs, you'd do something like:
EXEC sp_MSForEachDB
'SELECT ''?'' AS ''DBName'', sp.name AS ''dbo_login'', o.name AS ''sysdb_login'' FROM ?.sys.database_principals dp
LEFT JOIN master.sys.server_principals sp ON dp.sid = sp.sid
LEFT JOIN master.sys.databases d ON DB_ID(''?'') = d.database_id
LEFT JOIN master.sys.server_principals o ON d.owner_sid = o.sid
WHERE dp.name = ''dbo'';';
Conclusion:
When we do Database Migration then sometimes we see that the dbo of database from the original server was not copied/impersonated to new server correctly. This can be because of various reasons.
To check which databases are impacted we will run the below query
SELECT name AS DB, SUSER_SNAME(owner_sid) AS owner FROM sys.databases
If the result under column “Owner” is showing as null, or they are not having the correct DB owner name then run the below command on all such databases.
ALTER AUTHORIZATION ON DATABASE::Example TO sa;
SQL Server Error Code and solution summary
SQL Server Error: 15517
Severity: 16
Event Logged or not: No
Description:
Cannot execute as the database principal because the principal “%.*ls” does not exist, this type of principal cannot be impersonated, or you do not have permission.