This issue that I am talking about is a very common issue that I have seen. I have, in my experience, seen many users experiencing this issue; yet, most people do not know how to troubleshoot or fix this issue. Moreover, I have seen this issue on SQL Server 2005; although not tested, I am sure you might encounter this issue in SQL Server 2008 as well.
The issue is, you right-click on the database and choose properties. Now, instead of the database properties opening up, you get the message:
Cannot show requested dialog. (SqlMgmt)
Property Owner is not available for Database ‘[DBName]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
You do not face this issue with all databases; you can, in fact, see the properties of some of the databases and face this issue with some of the databases.
One reason for this message, is obviously due to insufficient rights. But, say, you are logged in into the SQL Server using a sysadmin account, yet you face this issue. What might be the reason? Correct, the reason is that there is no owner for this database.
If you run the command “sp_helpdb Affected_Datababase_Name“, you would see that the “owner” property would show a NULL value. This issue can happen if the owner of the database is dropped from the Security Logins of the SQL Server.
To fix the issue, run the following command against the affected database:
sp_changedbowner[ @loginame = ] ‘login’
[ , [ @map= ] remap_alias_flag ]
Permissions: Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server. [From Books Online]
Again, according to the Books Online:
- 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.
- The owner of the master, model, or tempdb system databases cannot be changed.
- To display a list of the valid login values, execute the sp_helplogins stored procedure.
- Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users that were previously assigned to dbo to this new database owner.
I hope this post is useful.
Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided “AS IS” with no warranties and confers no rights.