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.
Does anyone have anything *bad* to say about Hyperbac?
I've searched google, blown through every article there, blog entries, and everything. I can't find a single bad thing about the product.
Hell, Lance likes it. And I haven't ever seen Lance like any technology more advanced than bacon.
"Wheels suck. They square off, don't roll uphill on their own! I saw one roll backwards down a hill and over some guy's toe once. This is crap!"
Seriously.
"Fire's kinda OK, but I watched some guy get burned real bad once. I think I'll stay away from that."
And then Hyperbac:
"I've been testing that for over a year now and haven't found any problems."
Yeah. So either it (1) is completely suck free, or (2) they slipped Lance some really neat pills.
Thoughts?