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)
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)
43 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?
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.
“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?)
Excellent instructions. Thank you so much
Thanks a lot it works!
thank you worked for me
Thanks works fine
USE [DatabaseName]
GO
ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [sa]
GO
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.
Worked like a charm thanks dave.
Thank you, save my day!
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.
thank you so much sir, it works
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
Thnak you it realy helped me solve the issue. Thanks for the knowledge shared
Thanks! worked for me as well
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.
Worked Perfectly!
Yay! thanks for the tip.
Awesome. Thanks