Re: Change owner of SP
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/16/04
- Next message: OK: "Re: Function that returns table with dynamic code"
- Previous message: David Portas: "Re: Function that returns table with dynamic code"
- In reply to: Mark: "Re: Change owner of SP"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 16 Oct 2004 15:13:58 -0700
Hi Mark
Basically, what John was suggesting was that you just manually do what the
sp_MSforeachtable does. It creates a cursor that steps through the system
table 'sysobjects' finding all the user tables, and then uses them in
whatever command you specify. He suggested you just create a similar cursor,
but instead of looking for user tables, you look for procedures.
Here is a procedure that will do that for you. Make sure you are connected
as a SQL sysadmin before you create it in the master database.
------------------------------------------------
use master
go
create proc sp_MSforeachproc
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2
nvarchar(2000) = null,
@command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each procedure (optionally,
matching @where), with each table defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set
via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
if (@precommand is not null)
exec(@precommand)
/* Create the select */
exec(N'declare hCForEach cursor global for select ''['' +
REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' +
REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 ' + N' and
o.category & ' + @mscat + N' = 0 '
+ @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2,
@command3
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
return @retval
----------------------------------------------------------------
After you create the above procedure, USE the db where your procedures are
located and run the procedure just like you would run sp_MSforeachtable:
sp_msforeachproc "sp_changeobjectowner '?', 'dbo'"
This procedure is provided without warranties.
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Mark" <anonymous@devdex.com> wrote in message news:%23bEBNj8sEHA.2864@TK2MSFTNGP09.phx.gbl... > > Sorry John - I'm not an SQL Server programmer, so don't really > understand - just need to change the ownership - am I missing the point? > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
- Next message: OK: "Re: Function that returns table with dynamic code"
- Previous message: David Portas: "Re: Function that returns table with dynamic code"
- In reply to: Mark: "Re: Change owner of SP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
