Post reply

Property Owner is not available for Database - root cause?

  • March 16, 2011 at 1:12 pm

    #150058

    Hello,

    I am getting this error when I try to right-click in SSMS and check properties of a database.

    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. (Microsoft.SqlServer.Smo)

    I have seen several pages about how changing the owner can fix the error. But I haven't seen any explanations of why this happens, or how I can find out how it happened. I mean, there are suggestions that somehow the login or user associated with the db owner was dropped, but that concerns me since this is a production db server. How can such a drop happen without someone doing it? And if someone did it, how can I find out who did it?

    Thanks for any help.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • March 16, 2011 at 1:18 pm

    #1299614

    Typically this error occurs for one of two reasons.

    1. The owner of the database was a domain account that is no longer valid (such as a user's domain account)

    2. The owner of the database is set to nothing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 16, 2011 at 1:21 pm

    #1299617

    CirquedeSQLeil (3/16/2011)

    Typically this error occurs for one of two reasons.

    1. The owner of the database was a domain account that is no longer valid (such as a user's domain account)

    2. The owner of the database is set to nothing.

    Thanks for your quick response. Are these things that can happen due to patching/updates or do they have to be done manually or via human interaction? I can request to change the owner easily enough to try to fix the issue, but I am more concerned about who could have changed things, because the last time I checked the properties (a while ago, granted) I didn't see this error.

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • March 16, 2011 at 1:25 pm

    #1299621

    webrunner (3/16/2011)

    CirquedeSQLeil (3/16/2011)

    Typically this error occurs for one of two reasons.

    1. The owner of the database was a domain account that is no longer valid (such as a user's domain account)

    2. The owner of the database is set to nothing.

    Thanks for your quick response. Are these things that can happen due to patching/updates or do they have to be done manually or via human interaction? I can request to change the owner easily enough to try to fix the issue, but I am more concerned about who could have changed things, because the last time I checked the properties (a while ago, granted) I didn't see this error.

    Thanks again,

    webrunner

    Patching - No

    Human intervention - likely.

    In the case that it is a domain account - if the person whose domain account it is leaves the company and the account is disabled then you will run into this. There would be nothing malicious about it. It is far better to set the owner to sa or a domain service account that will not expire or be deleted.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 16, 2011 at 1:54 pm

    #1299630

    CirquedeSQLeil (3/16/2011)

    webrunner (3/16/2011)

    CirquedeSQLeil (3/16/2011)

    Typically this error occurs for one of two reasons.

    1. The owner of the database was a domain account that is no longer valid (such as a user's domain account)

    2. The owner of the database is set to nothing.

    Thanks for your quick response. Are these things that can happen due to patching/updates or do they have to be done manually or via human interaction? I can request to change the owner easily enough to try to fix the issue, but I am more concerned about who could have changed things, because the last time I checked the properties (a while ago, granted) I didn't see this error.

    Thanks again,

    webrunner

    Patching - No

    Human intervention - likely.

    In the case that it is a domain account - if the person whose domain account it is leaves the company and the account is disabled then you will run into this. There would be nothing malicious about it. It is far better to set the owner to sa or a domain service account that will not expire or be deleted.

    Thanks - now that I recall, it is likely the expiration of the login of a person who did leave in the recent past. It's highly likely that I just never checked the db properties since then.

    One final question. What is the best practice for setting db ownership? I have heard to set it to 'dbo' but I don't know why dbo is a user in the databases but not a login in the Security/Logins listing. Do you know why that is?

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • March 16, 2011 at 1:57 pm

    #1299632

    dbo stands for database owner. It's the user that all sysadmin logins map to. (and whatever login is set as the database owner)

    Recommendation for database ownership - sa. It'll always be there and setting it that way doesn't give anyone unexpected escalated privileges

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2011 at 1:58 pm

    #1299633

    I wouldn't set it to dbo - but that is me. Like I said, I would set it to sa or to a domain service account.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 16, 2011 at 1:59 pm

    #1299634

    On a related note, I think this is how to check my current databases to see if any are owned by anyone other than sa, correct? so this doesn't occur in the future?

    SELECT

    name,

    SUSER_SNAME(owner_sid) as susername

    FROM sys.databases

    WHERE SUSER_SNAME(owner_sid) <> 'sa'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 17, 2011 at 4:16 am

    #1299845

    Thanks, everyone, for this helpful information. I will go with setting the owner to sa as you recommended.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • March 17, 2011 at 10:12 am

    #1300048

    You're welcome

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply