Property Owner is not available for Database SSMS error

By:   |   Updated: 2011-08-17   |   Comments (7)   |   Related: > SQL Server Management Studio


Problem

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.

Additional information:
  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:

management studio
Solution

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. 

use master

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.

unknown

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.

track

If we run sp_helpdb again we can now see the owner is "sa".

owner

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.

database properties

Summary

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.

Next Steps
  • 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-08-17

Comments For This Article




Friday, July 3, 2020 - 5:19:22 AM - Chris Back To Top (86082)

Thanks, exactly what I needed!


Monday, December 23, 2019 - 1:07:29 PM - Charles Gedney Back To Top (83505)

Excellent article. I was having trouble figuring out why the DB was saying this.


Monday, June 29, 2015 - 9:23:50 PM - Joshua Back To Top (38069)

Thank you. This helped me solve my problem after removing accounts for a person no longer with the company who was apparently the owner of all databases on my server.


Wednesday, April 16, 2014 - 7:33:29 AM - dinesh Vishe Back To Top (30079)

Simple solution:-

use

 RMA

 sp_changedbowner

'sa'  -- change owner to SA

sp_helpdb

rma               -- see owner


Wednesday, April 16, 2014 - 7:31:43 AM - dinesh Vishe Back To Top (30078)

alter

database RMA setpartnerfailover

=>

Msg 1416, Level 16, State 1, Line 1

Database "RMA" is not configured for database mirroring.

 


Thursday, January 30, 2014 - 5:24:39 AM - Sowmya Back To Top (29281)

Awesome Manu... Thanks for sharing it. very clear and detailed explanation given.


Wednesday, August 17, 2011 - 11:42:43 AM - Steve Back To Top (14438)

*** NOTE *** - Hi thanks, It's a good article. I love the mirroring issue which you have written here. Its really going to resolve problems of most of DBA in prod.

 

 

If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.















get free sql tips
agree to terms