Change owner of all sps - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change owner of all sps

Author

Topic

vishal_7
Posting Yak Master

127 Posts

Posted - 2005-08-06 : 11:39:49

Hello,

I need to change the owner of all stored procedures from x to y. I know there is a sp_changeobjectowner sp, which does that, but in that way I will have to write a loop to get all sps which are owned by user x. Can somebody help me with that?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-06 : 14:47:26

try
select name = 'newowner.' + routine_name into #a from information_schema.routines
where routine_schema = 'olduser' and routine_type = 'PROCEDURE'

declare @name varchar(128)
select @name = ''
while @name < (select max(name) from #a)
begin
select @name = min(name) form #a where name > @name
exec sp_changeobjectowner @name, 'newowner'
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vishal_7
Posting Yak Master

127 Posts

Posted - 2005-08-06 : 16:10:31

perfecto. Thanks alot

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-06 : 16:35:26

of course should have been

select name = 'oldowner.' + routine_name into #a from information_schema.routines
where routine_schema = 'oldowner' and routine_type = 'PROCEDURE'

could also
select 'exec sp_changeobjectowner ''oldowner.' + routine_name + ''', ''newowner'''
from information_schema.routines
where routine_schema = 'oldowner' and routine_type = 'PROCEDURE'

and execute the result.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources