SQL SERVER – FIX: Database Diagram Error 15517 – Cannot Execute as the Database Principal Because the Principal ‘dbo’ Does Not Exist

Today’s blog post is directly inspired by the conversation I had during my Comprehensive Database Performance Health Check. During the consulting engagement, one of the developers reported below error in the database while trying to open a database diagram.

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)

As per message, it is clear that something is not right with database principal dbo. I asked history about it and learned that this database was restored from some other server. I was able to run some queries and find the issue.

To verify if you are running into the same issue, you can run below query to find who is mapped to “dbo” in the database. My sample database name is AdventureWorks, so please change accordingly.

USE AdventureWorks
GO
SELECT SUSER_SNAME(sid), * from sys.database_principals

If the first column shows as NULL then below fix would work.

WORKAROUND/SOLUTION

This was just a different variation of error which I have explained in my earlier blog. SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed

We went ahead and changed the owner of the database in the UI (Properties > Files tab)

SQL SERVER - FIX: Database Diagram Error 15517 - Cannot Execute as the Database Principal Because the Principal 'dbo' Does Not Exist db-diagram-err-01

Here is the equivalent command.

USE [AdventureWorks]
GO
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]
GO

After doing this, the issue was resolved, and they were able to use this feature?

How many of you use this old feature? Truly speaking, I have not seen many using it in production.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Server, SQL Server Management Studio, SQL Server Security, SSMS
Previous Post
SQL SERVER – FIX: The specified instance of SQL Server is hosted by a system that is not a Windows Server Failover Cluster(WSFC) Node.
Next Post
SQL SERVER – Unable to Set Cloud Witness. Error: An Existing Connection Was Forcibly Closed by The Remote Host

Related Posts

42 Comments. Leave new

  • Hi! I’m getting this error when trying to restore a database from another server. Is there any way I can remedy this during a restore?

    Reply
  • Sorry, but you can ignore the comment above. I was finally able to resolve it by changing authorization on the source database to SA before backing it up. Then when I restored, it didn’t give me this error.

    Reply
  • “How many of you use this old feature? Truly speaking, I have not seen many using it in production.”

    Can’t say I use it in production, but use it all the time when developing or maintaining code. I do use it only to view the database configuration, I do not use the modification features, seems a bit dangerous to use this to create/modify the database. But the visualization alone is super helpful, especially when working with a new project with an unfamiliar database.

    Pretty upset they removed it from SSMS v18. Guess I’m stuck with SSMS v17.9 until the project leader at Microsoft comes back to reality. (Also removed debugging. What are they thinking?)

    Reply
  • Dharma Dassanayake
    September 30, 2019 1:39 pm

    Excellent instructions. Thank you so much

    Reply
  • Thanks a lot it works!

    Reply
  • thank you worked for me

    Reply
  • Thanks works fine

    Reply
  • Rao Mubashir Noor
    December 6, 2019 6:05 pm

    USE [DatabaseName]
    GO
    ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [sa]
    GO

    Reply
  • programminginhell
    December 9, 2019 4:46 pm

    I agree. When working with large databases it helps a lot to mantain coherence and see flaws in the design. Also to have a better understanding about how data is stored.

    Reply
  • Worked like a charm thanks dave.

    Reply
  • Thank you, save my day!

    Reply
  • Definitely use it regularly. If the app DB has no data dictionary, and I am tasked with writing reports, it illustrates a decent number of joins, assuming the tables contain Primary and Foreign Keys…fastest way I know of to quickly learn to navigate a foreign database.

    Reply
  • sergen korkmaz
    March 19, 2020 1:36 am

    thank you so much sir, it works

    Reply
  • Thank you so much. I’m new to SQL and using ver 18.4 SQL Management studio with SQL Express version. strange they hadn’t fixed this issue for the AdventureWorksDW2017 db. Again, much gratitude, thank you.
    Carol

    Reply
  • MESSINA NTEDE
    April 19, 2020 9:48 pm

    Thnak you it realy helped me solve the issue. Thanks for the knowledge shared

    Reply
  • Thanks! worked for me as well

    Reply
  • Thanks, big help!

    To your question, yeah, I love this thing! It’s great for doing the initial design of a database and it also works great for getting a view on a database you don’t know. Just add all the tables to your diagram and SSMS will plop them down on a big diagram, connect them all according to foreign keys, and even arrange them nicely.

    Reply
  • Worked Perfectly!

    Reply
  • Yay! thanks for the tip.

    Reply
  • Awesome. Thanks

    Reply

Leave a Reply