Sql-server – How to resolve error 15517 when DBO is set to SA

sql serversql-server-2016ssms

Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated.

I'm trying to create diagrams for all the databases so I can try and get a grasp on how things are laid out here and a problem I'm running into is that when I attempt to create a diagram for one database in particular I'm getting the
following error:

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. (Microsoft SQL Server, Error: 15517)

I've been searching for more info on this issue and it looks like it usually is caused by the owner of the database getting deleted but this database shows SA as the owner so that shouldn't be the issue. I'm not even able to create a diagram if I sign in as sa…

Not sure if this may have something to do with it but this particular database was migrated to a new server a few months ago so something may have broken when that was done (although the database is live and in use so it isn't entirely broken).

Maybe its looking for the sa account from the old server and it just needs to be updated to use the sa account on the current server?

Edit: This DB may not actually have an owner after all. Performing a Right click > Properties > Files on the database shows no owner for the database even though the owner field under general does.

I tried to change it through the GUI and was informed that set owner failed with the following error:

Lock request time out period exceeded. (Microsoft SQL Server, Error:
1222)

I also tried the alter authorization ... statement and it just never completes (or I cancelled it after a few minutes to be more accurate). Am I being to impatient with that statement or is something else going on here?

Best Answer

Try this to 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 [*db*]
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.

Here is the sp_changedbowner doco

Related Question