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.
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)
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)