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:
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:
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.
That's a great post Suhas,
After going through the post, one can easily understand the importance of assigning an OWNER to all user DB's.
Looking fwd to more such posts...
Is the Login name suppose to be 'login' or something else?
[ @loginame = ] 'BOB'
Dude.. obviously the login name is not going to be 'login' !!!
It's going to be whatever the name of the user you want it to be!
The above doesnt work for me though since my log is full and im trying to access the properties to set the database into simple mode but since the db doesnt have an owner assigned, I am unable to do this. When I try to assign a owner, I get the same message I get with my other transactions which is the log is full!
Funny & ANNOYING!!!
I performed this procedure, assigning the sa as the dbo and when logged in as the sa I'm still getting the same error.
Can you run the following commands and let me know what you get as output?
Lookup the value of "owner" from the output. Now, execute the following command:
select name from sys.syslogins where loginname = '<owner>'
Please let me know what you get in each case.
Thanks for your help. On the first command, the owner value returned is NULL. So if I understand your second instruction I perform:
1>select name from sys.syslogins where loginname = 'NULL'
What is returned at that point is:
(0 rows affected)
Forgive me. I am only now learning SQL and have been assigned the role of DBA in my organization. It's quite daunting but I'm hungry to learn and be able to resolve these issues, so I appreciate your assistance.
Not a problem Tom. I am glad to be of any help to you... :)
So, now, you know what your problem is. Assigning a user as the dbo of a database does not help. You need to change the owner of the database. Execute the following command:
sp_changedbowner @loginame = 'sa'
Thsi will change the owner to 'sa', and you will be able to use the Management Studio to look up the properties of the database. You cna later change the owner of the database to who-so-ever you need from the database properties window.
That did it. Thank you very much for your very specific guidance. I have a lot of difficulty with syntax and the sql command line.
Your help is much appreciated.
Thats brilliant techinc .Thank you suhas.
Man, I'm so close... I follow everything here except running the commands against the database. Where/how do I run these commands against the database? From a cmd prompt? I don't see anything within SQL to be able to run these commands...
@Dodds: Please open a query window and execute the command from the query window.
From a command prompt, connect to the SQL Server Instance using SQLCMD or OSQL and then you can execute the command.
Hope this helps.
I'm using SMO calls to read the properties. I get "Property Size is not available for Database '[model]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. " error. When I tried "sp_helpdb model" I could see the owner as "sa" and db_size as 3.81MB. I do not see this problem for master database. PrimaryFilePath property is empty for master database but I do get the size. Could you help me get me out of this error?
It works for me.
I blog some additional here.
Perfect. Solved my problem immediately. Thank you!
HI Suhas De,
Thanks for the blog. Excellent really helped me with an issue i've come across from time to time. When I'm or any other dbs @ our company create db's I request that they use either 'sa' or the database admin general user. So I usually only come across this when Dev's have not followed the process on the dev environment.