How to change ownership for a set of stored procedures?

The following Microsoft SQL Server T-SQL sample script will change ownership from CurrentOwner to NewOwner for all stored procedures in Northwind database.

In SQL Server 2005 and SQL Server 2008 use ALTER SCHEMA or ALTER AUTHORIZATION instead.

-- SQL Server 2005 and on - Change stored procedure owner - QUICK SYNTAX

USE AdventureWorks;

ALTER AUTHORIZATION ON OBJECT::dbo.uspGetWhereUsedProductID TO MaryS

EXEC sp_help 'dbo.uspGetWhereUsedProductID'

/* Name                       Owner Type              Created_datetime

uspGetWhereUsedProductID      MaryS stored procedure  2010-05-09 06:14:15.700  */

------------

-- SQL Server change stored procedure owner

USE Northwind

GO

-- T-SQL cursor

DECLARE  @ProcName SYSNAME

DECLARE  @DynamicSQL VARCHAR(2000)

DECLARE curSprocNames CURSOR FAST_FORWARD FOR

SELECT name

FROM   dbo.sysobjects

WHERE  xtype = 'P'

OPEN curSprocNames

FETCH NEXT FROM curSprocNames

INTO @ProcName

WHILE (@@FETCH_STATUS = 0)

  BEGIN

    PRINT 'Changing ownership for ' + @ProcName

    

    SET @DynamicSQL = 'EXEC sp_changeobjectowner ''CurrentOwner.' + @ProcName + ''', ''NewOwner'''

    

    PRINT @DynamicSQL

-- T-SQL dynamic sql    

    -- EXECUTE (@DynamicSQL) -- uncomment for production

    FETCH NEXT FROM curSprocNames

    INTO @ProcName

  END

CLOSE curSprocNames

DEALLOCATE curSprocNames

GO

/* Partial messages

Changing ownership for Ten Most Expensive Products

EXEC sp_changeobjectowner 'CurrentOwner.Ten Most Expensive Products', 'NewOwner'

Changing ownership for Employee Sales by Country

EXEC sp_changeobjectowner 'CurrentOwner.Employee Sales by Country', 'NewOwner'

*/

Related links:

INF: SQL Server Procedure to Change Object Owner

Change the Owner for Stored Procedures