Home » SQL Server Error : 15517, Severity: 16. Cannot execute as the database principal

SQL Server Error : 15517, Severity: 16. Cannot execute as the database principal

sql server DBA 999

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.

Leave a Reply

Your email address will not be published. Required fields are marked *