Property Owner is not available for Database '[DBName]'.
Hi Friends,
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.
Comments
Anonymous
May 15, 2009
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...Anonymous
November 30, 2009
Is the Login name suppose to be 'login' or something else? [ @loginame = ] 'BOB'Anonymous
March 31, 2010
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!!!Anonymous
April 29, 2010
I performed this procedure, assigning the sa as the dbo and when logged in as the sa I'm still getting the same error.Anonymous
April 29, 2010
The comment has been removedAnonymous
April 30, 2010
Suhas, 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' 2>go What is returned at that point is: name
(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. Tom
Anonymous
April 30, 2010
The comment has been removedAnonymous
April 30, 2010
The comment has been removedAnonymous
September 23, 2010
Thats brilliant techinc .Thank you suhas.Anonymous
December 07, 2010
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...Anonymous
December 07, 2010
The comment has been removedAnonymous
October 13, 2011
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? Thanks, HemAnonymous
December 25, 2011
It works for me. I blog some additional here. chanmingman.wordpress.com/.../property-owner-is-not-available-for-database-database-nameAnonymous
September 19, 2012
Perfect. Solved my problem immediately. Thank you!Anonymous
November 15, 2012
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. Thanks Mark PointonAnonymous
June 03, 2014
Nice one! Another issue this would appear due to trans log shipping is full and once it is cleaned up, able to see properties tab of the database. ThanksAnonymous
August 19, 2014
The comment has been removedAnonymous
November 18, 2014
It was very use ful for me and thnks to TOmAnonymous
December 25, 2014
Ran the above command and got the output " The proposed new database owner is already a user or aliased in the database.". But still not able to get the properties of the db. still getting same error. Any suggestions?Anonymous
January 06, 2015
Hi Suhas, You saved me. Thanks a lot.Anonymous
January 22, 2015
@Suhas De. Thanks much for the answers and the post. Helped me. Bingo.Anonymous
February 08, 2015
This is happening to my tempdb database. The books online mentioned that the owner of the master, model, or tempdb system databases cannot be changed. Please advise.Anonymous
April 02, 2015
Even though database owner is sa, still it throws error: "Property Size is not available for Database '[DBNAME]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)"Anonymous
August 02, 2015
I am having the same issue. I am able to query using the sp_helpdb 'tempdb' and the DB is owned by 'sa'. I am getting the following error Property Size is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) Any help is appreciated.Anonymous
November 20, 2015
Thanks so much for this (still) timely post!Anonymous
June 21, 2016
Hi, I have a lot of databases in my sqlserver . While accessing some of them iam getting the property owner error. Does any one knows why this erorr has been occured .pls advise. i lost the permission to These corrupted database .- Anonymous
June 27, 2016
This can happen if a domain account was set as the database owner for those databases, and then the domain account is deleted or is removed from SQL Server. The ownership of the databases gets lost and you can get this error message.
- Anonymous