SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Troubleshooting SQL Server Error 15517

Last week I had the following error message repeating over and over again in the SQL Server log of one of my servers. It was repeating so much that the logs were growing very large, very fast:


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.


If you do a search for this error, you won't find a whole lot on it. You'll find that if you can figure out what database it is, then you should change the owner of the database.

First, check sys.databases:

Query sys.databases joined with sys.server_principals like so:

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; 

You're looking for databases owned by logins you know are no longer valid. We had a DBA recently retire and found a few that way.

Second, check to see if sys.database_principals and sys.databases match up:

We were still getting the errors and by looking at sys.databases, nothing was showing up as being wrong. However, that error means there is a mismatch with dbo matching up to a login. That means you have to take it a step further and query sys.database_principals and see how dbo matches up. For a particular DB, say Example, here's the type of query you'd run:

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'';';

By doing this, I found several databases that sys.databases said had an owner. However, when I checked it from the database's sys.database_principals, the SID didn't match up for dbo. The column I had for dbo_login came back NULL. That was a clear sign of the issue. There is also the possibility you will see a mismatch between dbo_login and sysdb_login. It appears that as long as dbo_login matches a legitimate login, the error is not generated. I found that on some DBs on one of my servers. While it's not causing a problem now, I'll be looking to correct the mismatch.

Correcting the Error:

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It's as simple as:



K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by jeffrey yao on 20 April 2013

Your post date is Apr 22, 2013, while now it is still Apr 20?

Posted by K. Brian Kelley on 22 April 2013

Jeffrey, it's because even though you schedule a post for a particular date on SSC, it still shows up and inserts into the RSS feed. However, via the web site it'll be dated and sort properly.

Posted by robert.bingham on 25 April 2013

Thanks for your article.  I get a NULL for dbo_login on one database, but when I run the alter authorization command I get this error:

Msg 15110, Level 16, State 1, Line 1

The proposed new database owner is already a user or aliased in the database.

Posted by yonastop on 21 September 2013

It works. you genius. thanx

Posted by nahmed8 on 2 May 2014

Thank you for this

Posted by christopher.lambe 47200 on 28 May 2014

Thx. Line 2 and Line 7 of the loop need the database name enclosed in square brackets otherwise databases with spaces in their names will produce errors.   [?] instead of []

Posted by Ritesh-123787 on 9 December 2014

I had issue in dropping the Publication with same error. I just change the owner of the DB and it worked. Thanks for the article

Posted by wish.mannat on 4 February 2015

same case with me, issue with dropping the publication, post helped a lot.

Can you also let us all know why does this happen. I mean under what circumstances does a database login become NULL?

Posted by lorenz.michels on 1 June 2017

Thanks! This was a solution to my problem as well. I got this error message after attaching a database from another machine. The error occured when I tried to look at or create database diagrams

Posted by m.katrobos on 21 March 2018

Thanks!  This fixed my problem trying to create a database diagram.

Leave a Comment

Please register or log in to leave a comment.