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
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.