Understanding Object Ownership

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/understandingobjectownership.asp

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • In an article like this the definition of dbo is a good thing to explain. BOL is vague about it: "The dbo is a user...". Does it mean a login or a server role, or anything else?

     

  • Hello,

    This is a good research work, thanks.

    I would also add that when a database is restored / moved from another server the db owner changes in most cases unless it is SA or Domain Login with access to both machines or a standard login explicitly created with the same SID on both machines (the list of exceptions is not possibly complete). We did see ownership issues in this case because DBO user is not mapped to any login which is not expected by most of developers if they move databases themselves in the development environment.

    Also many beginners do not realize that by default SQL EM (Enterprise Manager) has a local server registered in the security context of Windows  authentication. So is the default selection when registering the servers. So in many cases when they create objects it is thire Windows login that is the owner.

    Yelena.

    Regards,Yelena Varsha

  • DBO is a database user. There are no special rights on the server and it isn't a login. There is also a role called db_owner that DBO is a member of. Members of this role can do anything within a database.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi,

    Thanks for this article. It is clear and concise.

    Nate

  • Cannot agree more, nice and handy.

    I just wanted to say that it is neater to use EXEC in front of the stored procedure rather than GO at the end, so that the code may look like this:

    declare @OldOwner varchar(100)

    declare @NewOwner varchar(100)

    set @OldOwner = 'sys'

    set @NewOwner = 'dbo'

    select 'EXEC sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''' 

    from information_schema.tables where Table_schema = @OldOwner

    and the output may look like this:

    EXEC sp_changeobjectowner '[sys].[author]', 'dbo'

    EXEC sp_changeobjectowner '[sys].[OrderItem]', 'dbo'

    Just a very minor thing, thank you for the script. I used to do this using a cursor and PRINT command but it is much quicker this way.

    Thank you,

    Dmytro

  • Object Ownership has been something that has confused me for some time but now it is all clear. Thanks for submitting the article it was very easy to understand especially the chart!!!

    Paul Cyr

  • Glad I could help.  I am thinking about writing a new article on SQL Server 2005 schemas.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • Kathi,

    Thanks for the article, and looking forward to the 2005 version.  We've just started investigating the implications of the user / schema separation, and oooohhh..  there seem to be a few gotchas.

    Stephen

  • From the title of this article, I expected a discussion of various ownership strategies included in the article, the pros and cons of having dbo as the owner, as well as when it appropriate to have some other owner.

    Bill

  • Hi All,

    I ran across this article and it is nice. Howerver, I am still confused and need a bit of more information - to be honest, I am a bit of a beginner with SQL Server (normally I work with Oracle).

    My question is related to the fact that I am asked to revoke access for the developers to login into the SQL Server and use the SA account. They use the Remote Desktop and login with their domain accounts, but I don't think this is a good solution. My question is - can this be avoided?

    So I started testing a Client configuration, installed the Client part of MS SQL, so that a developer uses Query Analyzer on his PC, he can login to the databases using his domain account (without using Remote Desktop). I don't know if this is the right way to do it, but I searched and googled for at past two days and I cannot find a short explanation. How should this be done in a Domain environment, where developers should not have access to the server? What are the standards to work with a SQL Server?

    I don't suppose we should login from a client as SA or DBO. I use a domain test Windows account - let's call is tom.bear

    Now - tom.bear starts the query analyzer and uses Windows authentication. He is a member of db_owner, so he can create a table as DBO:

    create table dbo.new_table ( col int)

    And this works. But is this the way it should be set up? What is your experience?

    Thanks for any suggestions.

    (I edited this post because I missed something and it didn't work the first time)

    Regards,

    Richard

  • Hi Richard,

    Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.

    Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.

    Greg

  • You know, it really depends on the shop -- how job duties are divided. In small shops, sometimes the developer and DBA are the same person.

    The best practice would be for developers to ask the DBA to create the new objects and restrict the rights of the developers.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • It has nothing wrong for a user to access SQL Server using a domain account if the server is not a production server. You can set permission to his/her account according to your security policy.

    Normally, a user can only be a member of application role in a production server.

    In either case, a user shoiuld not have SA. Otherwise, our DBAs will lose our jobs.

    :hehe:

  • Greg Charles (5/16/2008)


    Hi Richard,

    Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.

    Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.

    Thanks Greg,

    very valuable info for me.

    Regards,

    Richard.

Viewing 15 posts - 1 through 15 (of 19 total)

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