using 'sp_' as a naming convention for stored procedures

From: Scott (anonymous_at_discussions.microsoft.com)
Date: 03/14/04 

Date: Sun, 14 Mar 2004 15:07:03 -0800

I know that using 'sp_' as a naming convention for stored
procedures is not a good idea because MS uses that naming
for system stored procedures. I am working on a system
where the original developer had all of the stored
procedures named "sp_" then the name. I am being told
that this is causing a problem with the server because of
it. The DBA group told me that when using "sp_" as a
name for a stored procedure it automatically causes the
call to go to the master DB to look it up. I have never
heard of this before.

Has anyone else?

Additional information:
In my connection string I specify the DB name.
I do not reference any tables outside of the DB the sproc
resides in.

Any feedback would be appreciated.

thanks
Scott

http://support.microsoft.com/default.aspx?scid=kb;en-
us;159901
The "Transact-SQL Reference," under the User-defined
System Stored Procedures heading, states:

When executed, a stored procedure is typically found
within the current database. If the procedure name begins
with sp_ and is not found in the current database, SQL
Server will look in the master database.

This statement incorrectly implies that you can create
user-defined system stored procedures outside of the
master database. The statement in Example H of the CREATE
PROCEDURE Examples section is correct: "user-defined
system stored procedures...must be created by the system
administrator in the master database."

Relevant Pages

  • Re: system stored procedures
    ... The stored procedures sp_MSdbuserpriv and sp_MSuseraccess ... legitimate system stored procedures. ... >standard login, give that login access to the user ... >database and also default the login to the user database. ...
    (microsoft.public.sqlserver.security)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures
    ... Newer versions have System Stored Procedures that can extract ... Launch the PCC, and in the tree view on the left-hand side, expand ... Wayne Freeman ... several years in VB4 that is making calls to stored procedures. ...
    (comp.databases.btrieve)
  • System Stored Procedure Permissions
    ... I have system stored procedures in a databse ... somehow instead of showing the owner as dbo and type as ... database. ...
    (microsoft.public.sqlserver.security)
  • Execute Persmission denied on object sp_OACreate
    ... I have a user who has execute permissions on a store procedure in a database ... which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)