Erik Porter's Blog

Life and Development at Microsoft and Other Technology Discussions

News

    Change Owner of an Object in SQL Server 2000

    This is mostly just a post for me so I never forget this, but to change the owner of an object in sql server, use sp_changeobjectowner, just pass it the name of the object (as a string) and the name of the new owner (as a string)

    UPDATE: Thanks to Matthew Gibson for correcting my typo on the stored procedure to call.

    Posted: Oct 29 2004, 12:13 PM by HumanCompiler | with 14 comment(s)
    Filed under: ,

    Comments

    matthew gibson said:

    sp_changeobjectowner is how to change the owner of an object. sp_changedbowner is how to change the owner of the db itself, not the objects in the db. :)

    # October 29, 2004 4:21 PM

    Erik Porter said:

    You're absolutely right, I was looking at both of them when I wrote this and copied the wrong one...thanks for the correction...
    # October 29, 2004 7:17 PM

    David Levin said:

    Thanks so much! Exactly what I was looking for.

    # March 18, 2007 10:29 PM

    Bernd Gaucke said:

    Hello!

    That's exactly what I'm looking for, too, but i need to change the ownership of ~250 objects, is there a way to use the result of a query like "select name from sysobjects where xtype like 'U'" to feed the stored procedure with??

    Thanks, Bernd

    # July 17, 2007 7:01 AM

    LeAnne said:

    Thanks - this did the trick for me, too.  One comment, though:  I came from an Oracle background, and thought I had to put parens around the arguments.  So, to note for other sql server newbies, it's just:

    exec sp_changeobjectowner 'tablename', 'dbo'

    and not:

    exec sp_changeobjectowner( 'tablename', 'dbo' )

    # September 18, 2007 4:23 PM

    Michael Muller said:

    Awesome. Saved me HOURS of pain and heartache.

    sp_changeobjectowner 'tablename', 'dbo'

    # December 16, 2007 4:13 PM

    Vishnu said:

    Wonderfull. Am worrying for days how to transfer ownership. You saved my time. Thank you.

    # February 4, 2008 7:54 PM

    Ian said:

    For the n00bs, you need to have the full "path" to the object as part of the syntax (at least with SQL 2000).

    E.g:

    sp_changeobjectowner 'dbname.dboOwner.object name', 'dbo'

    # February 4, 2008 8:23 PM

    S said:

    Thanks. This was very helpful and saved me a lot of time.

    # April 8, 2008 2:14 PM

    Anwar Sayeed said:

    To change all the objects owned by user test to dbo, run the following code:

    exec chObjOwner 'test','dbo'

    it will list the lines of execution string to call for sp_changeobjectowner for all the objects owned by the user.

    have fun!...

    Anwar Sayeed

    # June 17, 2008 7:12 AM

    Tim said:

    The full chObjOwner script by Micorosft is listed here:

    http://support.microsoft.com/?scid=kb%3Ben-us%3B275312&x=12&y=16

    # July 2, 2008 1:59 AM

    Jim Ezzell's Blog said:

    Change Owner of an Object in SQL Server

    # August 15, 2008 9:14 PM

    Pradeep said:

    Good help

    # November 12, 2008 12:09 AM

    AR said:

    Thanks for the help.  Worked for me.

    # December 18, 2008 12:15 PM