2

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?

0
3

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

1

This is slightly reframing your question, but I would actually just avoid using the Database Diagram feature. It has been deprecated and removed from the next version of SSMS (v18):

The following features are no longer available in SSMS:

  • ...
  • Database Diagrams

Note in the screenshots in this blog post that the feature is not available (so it's not just deprecated / discouraged, but actually removed from the product).


To answer you question as asked though, that GUI action uses the ALTER AUTHORIZATION command behind the scenes, so you were on the right track there. SSMS just won't wait around forever, so it bails out with that timeout error.

You likely need to have exclusive access to the database in order to change the owner. So you'll need to set the database to SINGLE USER mode and then run the command.

Since you mentioned that you are new to all this, I will explicitly say that you should only do this during scheduled downtime! As it will disconnect all other users, applications, scheduled jobs, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.