By: Sergey Gigoyan | Comments (1) | Related: 1 | 2 | 3 | 4 | > 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.
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:
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":
When the "Database Properties" window opens, we can see on the "General" tab that the owner is "MYPC\Administrator" user:
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:
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":
Also, when we connect to the SQL Server instance, we mention the server's new name and use "DBSERVER1\Administrator" for the Windows login:
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":
Then, after typing the new name of the login, we can see that we have "DBSERVER1\Administrator" login in our login's list:
After that, if we open the TestDB database's properties, we will see that the owner's name is automatically changed:
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:
Now, we can successfully create and save a database 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:
- https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/create-a-new-database-diagram-visual-database-tools?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-transact-sql?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/design-database-diagrams-visual-database-tools?view=sql-server-ver15
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
