MS SQL Server security - sp_procoption and listing all stored procs config to run @ startup

Author

sp_procoption and listing all stored procs config to run @ startup

MSSQLServerDeveloper

2005-05-18, 1:23 pm

I was told that running the following from the master db will give me a list
of all the stored procs that are set to run at startup.

sp_procoption null,'startup'

However, when I attempt this command, I get the error -
Procedure 'sp_procoption' expects parameter '@OptionValue', which was not
supplied.

Anyone know how to get this to work??

Jasper Smith

2005-05-18, 1:23 pm

Looking at the code for sp_procoption that doesn't sound correct. You can
simply use

select name from sysobjects
where objectproperty(id,'E
xecIsStartup')=1

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

" MSSQLServerDeveloper
" < MSSQLServerDeveloper
@discussions.microsoft.com>
wrote in message news:B91CD1C5-F72A-4C50-B153- 5D418BF1AC16@microso
ft.com...
>I was told that running the following from the master db will give me a
>list
> of all the stored procs that are set to run at startup.
>
> sp_procoption null,'startup'
>
> However, when I attempt this command, I get the error -
> Procedure 'sp_procoption' expects parameter '@OptionValue', which was not
> supplied.
>
> Anyone know how to get this to work??

Chris Wood

2005-05-18, 1:23 pm

From BOL.

Transact-SQL Reference

sp_procoption
Sets procedure options.

Syntax
sp_procoption [ @ProcName = ] 'procedure'
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'

Arguments
[@ProcName =] 'procedure'

Is the name of the procedure for which to set an option. procedure is
nvarchar(776), with no default.

[@OptionName =] 'option'

Is the name of the option to set. The only value for option is startup,
which sets stored procedure for autoexecution. A stored procedure that is
set to autoexection runs every time Microsoft® SQL ServerT is started.

[@OptionValue =] 'value'

Is whether to set the option on (true or on) or off (false or off). value is
varchar(12), with no default.

Return Code Values
0 (success) or error number (failure)

Permissions
Execute permissions default to members of the sysadmin fixed server roles.
Startup procedures must be owned by the database owner in the master
database.

Chris Wood
" MSSQLServerDeveloper
" < MSSQLServerDeveloper
@discussions.microsoft.com>
wrote in message news:B91CD1C5-F72A-4C50-B153- 5D418BF1AC16@microso
ft.com...
>I was told that running the following from the master db will give me a
>list
> of all the stored procs that are set to run at startup.
>
> sp_procoption null,'startup'
>
> However, when I attempt this command, I get the error -
> Procedure 'sp_procoption' expects parameter '@OptionValue', which was not
> supplied.
>
> Anyone know how to get this to work??

begin 666 mailto.gif
M1TE&.#EA' `2`/<`````_X2$_YS. ____________________
____________
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
__RP`````' `2```(8 `%"!Q(L*#!@P@3*ES(L*%#
MA@``!)!(<:)%B@`6!AC L:/'CQD5`A@0,> )'CA-):CQYTF3(A"--LO3X$N%&
DFB5E<JQY<.3,EBMQE@2YT.=/FD5S*EW*\Z'3IU"C2D48$ `[
`
end

begin 666 keybrd.gif
M1TE&.#EA' `2`/<`````````_X2$_YS. ____________________
________
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
__RP`````' `2```(8P`'"!Q(L*#!@P@3*ES(L*'#
MAQ 1"IA(L:)%B@`2"B 0@*/'CB ]$A"046* DRA3J@Q 4B.!CS!#=FQI<J7-
GDS0/;H3YLF? (D25U7AQ:,:A!`$B3*EV
*-&?$IU"C2IU*56! ```[
`
end

begin 666 relglyph.gif
M1TE&.#EA' `2`/<`````````_X2$_YS. ____________________
________
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
____________________
____________________
_
M___________________
__RP`````' `2```(=0`'"!Q(L*#!@P@3*EQ8,(##
M```8+A1 H& )$B0D%:'P(42''AP,I$A
!)X"+"CP$(!!"HL:5&DP=)JJRX4B1)
MF 8I.E0)$H!/ES@+RJRHDN!- A3HY%AWHLRE2HE!78C3*
\^'2J2RA$I6*=0!*
,D%W#BAU+5F! ```[
`
end

Sponsored Links