Unable to Open SQL Server Database Properties Window as it Returns Error

SQLServerF1

SQL Server Database Property page can be used to view or modify options for the database like changing the Collation, Recovery model, Compatibility level, Auto Close, Auto Create Statistics, Auto Shrink, etc. Sometimes you may receive below mentioned error when you right-click on the database and click on properties option.

Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘TestDB’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

This issue may not happen with all databases, as you may be able to open other databases properties on same SQL Server instance without any errors.

The reasons why this error may appear could be that there is no owner for this database either because the login has been deleted from the SQL Server or if it is a windows or domain account then the account may have been removed from the AD. Also it may be possible that SQL Server cannot validate this account due to permission or connectivity issues with AD.

As you are not able to use the GUI to check the database properties, so we cannot see if there is Database owner present for the database and if present then what is the name of that account. Other options to verify the owner of a database is by running the below query.

sp_helpdb [DBName]

Once you identify that there is no database owner or the login does not exist any more, you can run the below command to change the database owner to a different account.

Use [DBName]
GO
EXEC sp_changedbowner ‘SA’;

After running the above command you can Now you can now open the properties of the database without any error. Instead of ‘SA’ account you can choose any login which exists in the SQL Server instance and is valid, However it is important to note that after sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database. Meaning the database owner can perform all operations inside the database inclusing dropping the database itself.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *