GeekZilla

Change the Owner for Stored Procedures

This one comes from Tim Page, if you ever need to change the owner of several Store Procedures at the same time then this is the T-SQL for you. And this even supports SQL Sever 2005.

DECLARE
  @OldOwner sysname,
  @NewOwner sysname

 SET @OldOwner = 'oldOwner'
 SET @NewOwner = 'dbo'

DECLARE CURS CURSOR FOR
SELECT
  name
FROM
 sysobjects
WHERE
  type = 'p' 
AND
  uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) 
AND 
  NOT name LIKE 'dt%' FOR READ ONLY

DECLARE @ProcName sysname

OPEN CURS

FETCH CURS INTO @ProcName

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@VERSION >= 'Microsoft SQL Server 2005'
    BEGIN
        EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
        exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
    END
    ELSE
        EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

    FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS

Author Greg Duffield

Greg has too many years experience in developement from VB for DOS all the way through to Windows Workflow and LINQ while covering pretty much every technology in between. A complete MS evangelist he is now Director of the only MS Gold Partner IT services company in Norfolk. Wehere they are producing Web 2 Applications for various sectors, and are currently entering the Haulage industry with their latest product.

Comments

Add Comment

Latest Articles