Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Property Owner is not available for Database SSMS error


By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Studio

Problem

When you try to launch the database 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:

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. 

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.

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.


Last Update:






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.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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

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

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


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

*** 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.


Learn more about SQL Server tools