Property Owner is not available for Database SSMS error
By: Manvendra Singh | Updated: 2011-08-17 | Comments (6) | Related: More > SQL Server Management Studio
When you try to launch the databae mirroring GUI or some other database property window in SSMS you get this error:
Cannot show requested dialog.
Cannot show requested dialog.(SqlMgmt)
Property Owner is not available for Database'[XXXX]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Here is a screenshot of the error:
I saw this issue during our DR (Disaster Recovery) exercise. We were using the SSMS GUI and we successfully failed over one database from the principal server to the mirror server, but when we did a failback via the GUI we got this error.
It seems that issue occurs only in the GUI mode for database mirroring, so we were able to do a failback using T-SQL as shown below.
To diagnose this issue I decided to check the properties of this database after the failback using the GUI and I got the same error.
I then ran the system stored procedure sp_helpdb to check the database properties and noticed that the owner column was set to UNKNOWN as shown below. When this database was initially setup it was set to a valid Windows login, but that login has since been dropped because that person left the company. So in this case SQL Server assumes it is UNKNOWN and this is what is causing the error message in the GUI.
To fix this we needed to change the database owner from UNKNOWN to a valid login. This can be done using sp_changedbowner. Here is the T-SQL command to change the database owner to "sa" for the TRACK database.
If we run sp_helpdb again we can now see the owner is "sa".
Now if I launch the mirroring GUI again it works fine without the error message. So by changing the database owner property for the database you will have access to make the failover via the GUI.
If your database owner property is not set to a valid login then you will not be able to launch the GUI property page whether it's for:
- database properties,
- database mirroring,
- log shipping
- or any other database property window.
This issue occurs in all versions of SQL Server when opening a GUI page that needs a valid database owner. Since you cannot set your database owner using the GUI to fix this issue, you have to use T-SQL to resolve this issue using the sp_changedbowner command.
- Make sure the database owner is not UNKNOWN for any database. The best thing is to create a standard login for your environment and use this login as the database owner for all your databases. Evaluate the database owner property for all databases in your environment and set to a valid login to avoid any future issues.
Last Updated: 2011-08-17
About the author
View all my tips