Registering a SP in master?

(SQL Server 2000, SP3a)
Hello all!
A while ago I seem to recall a post that talked about the possibility of registering a
stored procedure in the master database, such that it became a "global" procedure with
public scope without having to prefix the execution with "master". However, I can't seem
to figure out how to perform this registration process.
Any ideas?
John PetersonAny procedure in master, that is owned by dbo, and begins with sp_ will be
accessible from any database without specifying the db or owner name.
However, you still must grant permissions to those users who are allowed to
execute the procedure.
USE Master
CREATE PROC sp_myproc
AS ...
RETURN
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> A while ago I seem to recall a post that talked about the possibility of
registering a
> stored procedure in the master database, such that it became a "global"
procedure with
> public scope without having to prefix the execution with "master".
However, I can't seem
> to figure out how to perform this registration process.
> Any ideas?
> John Peterson
>|||Ah, gotcha! So kind of by virtue of residing in master and having the "sp_" prefix and
being owned by dbo, it'll be accessible to all other databases without having to
explicitly prefix the database or owner name. Thanks, Kalen! (And thanks for the
reminder about the permissions!)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Any procedure in master, that is owned by dbo, and begins with sp_ will be
> accessible from any database without specifying the db or owner name.
> However, you still must grant permissions to those users who are allowed to
> execute the procedure.
> USE Master
> CREATE PROC sp_myproc
> AS ...
> RETURN
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > A while ago I seem to recall a post that talked about the possibility of
> registering a
> > stored procedure in the master database, such that it became a "global"
> procedure with
> > public scope without having to prefix the execution with "master".
> However, I can't seem
> > to figure out how to perform this registration process.
> >
> > Any ideas?
> >
> > John Peterson
> >
> >
>|||Yes, sp_ actually stands for 'special' because it gives your objects a
special behavior. :-)
Another note on permissions... permissions are granted to users, and most
database logins do not have specific user names in master. So your choices
really are between not granting permission to anyone, so only 'sa' can use
the procedure, and granting to public, so everyone can. Most of the system
procs (sp_help%, etc) have execute granted to public.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ujiLi6dkDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Ah, gotcha! So kind of by virtue of residing in master and having the
"sp_" prefix and
> being owned by dbo, it'll be accessible to all other databases without
having to
> explicitly prefix the database or owner name. Thanks, Kalen! (And thanks
for the
> reminder about the permissions!)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Any procedure in master, that is owned by dbo, and begins with sp_ will
be
> > accessible from any database without specifying the db or owner name.
> > However, you still must grant permissions to those users who are allowed
to
> > execute the procedure.
> >
> > USE Master
> > CREATE PROC sp_myproc
> > AS ...
> > RETURN
> >
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > A while ago I seem to recall a post that talked about the possibility
of
> > registering a
> > > stored procedure in the master database, such that it became a
"global"
> > procedure with
> > > public scope without having to prefix the execution with "master".
> > However, I can't seem
> > > to figure out how to perform this registration process.
> > >
> > > Any ideas?
> > >
> > > John Peterson
> > >
> > >
> >
> >
>