Visualising the tables and foreign keys inside a database is often a great help to understand what is going on. While there are many options to create such a diagram, the built-in feature of SQL Server is often all I need. However, since my last installation of my working machine I cannot create these diagrams. All I get is this error message:
I checked all places where SQL Server shows me the owner of a database and everywhere is my user the owner. It took me some time, but finally I found a command that fixed this error:
|
1 |
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2012 TO [sa] |
ALTER AUTHORIZATION changes the owner of an object. In the example above the user sa is now the owner of the AdventureWorks2012 database. Change the name of the database to the one you have troubles with and it should fix your problem as well. (It does not work if I replace sa with my database user)
After this little command I can right-click on the Database Diagrams node and create the required support objects:
When this command is successful, the diagram feature works as expected:
Just do not forget that changes in those diagrams are applied to the database and it does not take much to drop tables or foreign keys…
2 thoughts on “Little SQL Server Tricks: How to Fix “Database Diagram Support Objects Cannot Be Installed””