DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE