Post reply

Removing database owner

  • September 16, 2014 at 7:36 am

    #299438

    Hi,

    I just wanted to know, if a database or SQL Server job has a particular owner and this user leaves the company and their AD account is deleted, I know access for users will become limited to that particular database object.

    To what extent will access be limited?

    So if I was a 'normal' user in what ways would I be restricted? For example would I be able to view data but not edit it? Or for a job would I be able to run the job but not edit it?

    What would be the situation if I was a sysadmin on the instance and the user who owned the job and database left the company and was deleted from AD.

    What restrictions would apply to me as a sysadmin if I wanted to access/modify these database objects?

    Thanks.

  • September 16, 2014 at 8:07 am

    #1745785

    zedtec (9/16/2014)

    I know access for users will become limited to that particular database object.

    No. If the database's owner is not a valid AD account, most things will work fine. Some stuff, like replication, will break with nonsensical errors, but access won't be limited, certainly not to a sysadmin.

    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

  • September 16, 2014 at 8:18 am

    #1745793

    Ok if the database owner is a valid AD user and is deleted from AD will there be any restrictions in this scenario?

    If so what would they be?

  • September 16, 2014 at 11:03 am

    #1745842

    You can have odd errors occur in that situation.

    You're better off having a fixed, permanent owner for dbs and granting permissions as needed to other users, up to and including db_owner if that is what they require.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • September 17, 2014 at 2:29 am

    #1745969

    Ok thanks.

  • September 17, 2014 at 2:34 am

    #1745972

    zedtec (9/16/2014)

    Ok if the database owner is a valid AD user and is deleted from AD will there be any restrictions in this scenario?

    That's the scenario I described...

    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

Viewing 6 posts - 1 through 5 (of 5 total)

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