Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

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

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:

 

Message
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:

ALTER AUTHORIZATION ON DATABASE::Example TO sa;

 

Comments

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

Leave a Comment

Please register or log in to leave a comment.