17 February 2009

Script to change object owners

I have recently started working with SQL Server 2005, and the first thing I noticed is that I cannot automatically set the owner of a script at creation time.  SQL Server 2005 automatically adds my user name as the object owner.

The following script, adapted from Scott Forsyth's blog, batches up the changes.  Change the name of the old owner (@old) and the new owner (@new) and it will change the ownership for all the database objects.

Script to update SQL Server Object Ownership

DECLARE @old nvarchar(30), @new nvarchar(30), @obj nvarchar(50), @x int;

SET @old = 'domain\user-name';

SET @new = 'dbo'SELECT @x = count(*);

FROM INFORMATION_SCHEMA.ROUTINES a

WHERE

  a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

  AND a.SPECIFIC_SCHEMA = @old;

while(@x > 0)

  BEGIN

    SELECT @obj = [SPECIFIC_NAME]

    FROM INFORMATION_SCHEMA.ROUTINES a

    WHERE

       a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

       AND a.SPECIFIC_SCHEMA = @old;

     SET @x = @@ROWCOUNT - 1;

    EXEC sp_changeobjectowner @obj, @new;

END