Post reply

Alter Authorization on Schema Statement

  • May 7, 2014 at 8:01 pm

    #307148

    Hi Friends.

    I was unable to delete a user because they were db_owner for a particular database. I entered the following statement below:

    ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

    After that I was able to delete them. My question is should I have done the following instead?

    ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;

    What's the difference between assigning authorization to dbo or db_owner?

    Thanks for your help.

  • May 10, 2014 at 10:42 am

    #1712398

    "dbo" has two interpretations:

    a) the object "dbo" is the default schema of a database and

    b) the principal "dbo" is the default user when a database is created that will be assigned to the db_owner database role and will also be the owner of the dbo schema.

    "dbo_owner" has two interpretations, too:

    a) the object "dbo_owner" is a schema of a database and

    b) the principal "dbo_owner" is a standard role when a database is created that will be the owner of the dbo_owner schema.

    Interestingly enough SQL Server assigns each schema it creates when running CREATE DATABASE to a database role of the same name.

    Except for the schema dbo. This is assigned to the user dbo instead of a role. Seems to be a "left-over" from SQL Server 2000 and before...

    From my point of view it's a good advice to have a schema ownership assigned to a role instead of a user.

    Therefore, ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner; would have been the better option.

    But I can't find any drawback when using dbo instead of db_owner...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 10, 2014 at 4:49 pm

    #1712421

    Thanks for the information. Very helpful!!!!!

Viewing 3 posts - 1 through 2 (of 2 total)

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