SQL Server Management Studio Database Diagram Support Objects Cannot be Installed


By:   |   Updated: 2020-12-02   |   Comments   |   Related: More > Database Design


Problem

Sometimes there is a need to rename a host machine after installing SQL Server. After having changed the hostname and having made any related changes, we can successfully connect using the new name and work with SQL Server without any problems. However, there can be a hidden problem that you may face when creating a database diagram. Below is an error message you can receive, that it is not possible to create a database diagram as the database does not have a valid owner.

ssms error message

In this article, we are going to reproduce and explain how to fix this issue for the error message "database diagram support objects cannot be installed because the database does not have a valid owner".

Solution

Suppose we installed SQL Server on a machine and after that we renamed the host computer from "MYPC" to "DBSERVER1". In our example, we have a default SQL Server instance and we mention the server's new name to connect - "DBSERVER1". If we try to create a database diagram, for example, for TestDB, we will receive the following message:

ssms database diagram error

Well, it is mentioned that the diagram cannot be created as the database does not have a valid owner. So, let's check who is the database's owner. To do that, we perform a right-click on the database and choose "Properties":

database properties

When the "Database Properties" window opens, we can see on the "General" tab that the owner is "MYPC\Administrator" user:

database properties

We can find the corresponding login under "Security" > "Logins". There we can find "MYPC\Administrator" login and if we open its properties, we can see that it has a "db_owner" role member for TestDB:

logins

As we already know, the computer's name is not "MYPC" anymore and therefore, we must not have such a user. In computer properties we can double-check and confirm that the computer has a new name – "DBSERVER1":

computer name

Also, when we connect to the SQL Server instance, we mention the server's new name and use "DBSERVER1\Administrator" for the Windows login:

connect to sql server

We can guess that "MYPC\Administrator" has become an invalid name for the login. Therefore, we should change it to the correct name to "DBSERVER1\Administrator". We can do that by right-clicking on the login and choose "Rename":

logins rename

Then, after typing the new name of the login, we can see that we have "DBSERVER1\Administrator" login in our login's list:

logins

After that, if we open the TestDB database's properties, we will see that the owner's name is automatically changed:

database properties

Hence, we have set the right name for the database owner, and creating a database diagram should work now.

If we retry to create a database diagram, we can see that we do not receive the previous error message and the diagram creation window appears:

create diagram

Now, we can successfully create and save a database diagram:

create diagram

Thus, our problem is easily fixed by changing the legacy name of the Windows login that owned the database according to the host computer's new name.

Conclusion

In conclusion, if a database is owned by a login, the name of which has become invalid after renaming the host computer, it will cause some problems. Being unable to create a database diagram is one such example. Therefore, if a computer is renamed after installing SQL Server on it, along with other related changes, it is also important to rename the corresponding logins that use the computer's previous name.

Next Steps

Please use the links below to find additional information about the discussed topic:






get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips


Article Last Updated: 2020-12-02

Comments For This Article





download














get free sql tips
agree to terms