SQLUSA

Microsoft SQL Server 2000 Best Practices

 

How to change ownership for a set of stored procedures?

 

The following sample script will change ownership from CurrentOwner to NewOwner:

 

USE DatabaseX
GO

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 = 'sp_changeobjectowner ''CurrentOwner.' + @ProcName + ''', ''NewOwner'''
PRINT @DynamicSQL
EXECUTE (@DynamicSQL)
FETCH NEXT FROM curSprocNames INTO @ProcName
END

CLOSE curSprocNames
DEALLOCATE curSprocNames
GO

American Standard in SQL Server Training
 
SQLUSA.com Home Page