locked
Permission requierd to change an object owner RRS feed

  • General discussion

  • hello all ,

    i tasked to run some of scripts using ALTER.. my question is what is i but the default object owner before object name [dbo].[objectname] do i need to have an admistartion privialge on the whole instance or just need to be only db admin

    Tuesday, March 29, 2011 7:41 PM

All replies

  • No, instance adm permissions are not necessary.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Tuesday, March 29, 2011 10:35 PM
  • BOL says

    The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

    For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.

    The dbo user cannot be deleted and is always present in every database.

    Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

    • Belong to the user creating the object, not dbo.

    • Are qualified with the name of the user who created the object.

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 30, 2011 6:39 AM
  • Hi Guys,thanks for your replies

    as you stated Mark No instace admin permission , dbo_owner on the database is enough to do the job....

    thanks you Uri for your clarificatiosns but meaning that it shouldn't work and i had to change user permission to Sys_admin agroup ccording to BOL!!!??

    Sunday, April 3, 2011 8:00 AM
  • No, if you db_owner you need just specufy CREATE TABLE dbo.t1(c INT)
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, April 3, 2011 8:30 AM
  • The specific permission you need is the TAKE OWNERSHIP permission. As in GRANT TAKE OWNERSHIP ON OBJECT::<object_name> TO <user_name>. The TAKE OWNERSHIP permission is included in the CONTROL permission on the object, the CONTROL permission on the objects schema, the CONTROL permssion on the database (which is probably too much), and the CONTROL SERVER permission (which is obviously way too much).

    To change the owner, use the ALTER AUTHORIZATION statement.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, April 4, 2011 10:08 PM