Chris Miller Blog

I saw Mark's post earlier bringing up how you're not supposed to use sp_ as the start of a stored procedure name….

Here's *why* it does that.

Go to any user database (NOT MASTER)  you have and put in a proc like this:
create proc sp_addlogin
as
begin
    select 'hah, PWND!'
end
go

And then try to run it.

You get an error message back:
Msg 201, Level 16, State 4, Procedure sp_addlogin, Line 0
Procedure or function 'sp_addlogin' expects parameter '@loginame', which was not supplied.

Which is a good thing.  Notice that it's running the sp_addlogin in *master* and not the one in your local user database.  The interesting thing is that if you try to run it directly:
exec monitoring..sp_addlogin

You get the same message back. 

You cannot create a stored procedure with the same name as a system stored procedure and have it run.  It doesn' t matter what database you put it into.

So the question becomes *why*.  Here's why:

create proc sp_addlogin
  @Loginame sysname,
  @passwd sysname,
  @defdb sysname,
  @deflanguage sysname='',
  @sid uniqueidentifier,
  @encryptopt nvarchar(500)
as
begin
declare @rc int

insert into ImAnAwesomeHacker values (@Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt)

exec @rc = master..sp_addlogin @Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt

return @rc

end
go

(Ignore my lack of a field list on the insert, this is just an example and won't work anyway)

And now you can capture *every* single username and password in cleartext.  Grats.

Well, you can't do that, because your uberl33t haxor won't work because your stored procedure called sp_addlogin will never get called.  Curses, foiled again!  This does, however, illustrate pretty succinctly exaclty why SQL Server always checks master or sp_ names. 

In older versions of SQL Server (i.e. 4.2 to 7.0 I think) if you named a stored proc sp_ and had one named the same in master it would *always* run the one in master.  It doesn't do that anymore, but it still has to check master to see if you're trying to play games with a system proc.  It only does this for stored procedures that start with sp_, so this is a very small but still somehow measurably important performance optimization.  I think they changed the behavior because too many cretins use sp_ for stored procedure names and then forget to change database context away from master when they're updating stored procedures, which back in the olden days was done strictly with create/drop.  The interesting problems that could result are left as an exercise for the reader.