1

I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure. As usual, I right-clicked it in SSMS, clicked Script stored procedure as... then create, and to my surprise, all I found was the CREATE PROCEDURE statement: enter image description here

To double-check, I had someone else look and they got the same thing. They were signed in as database owner, I was signed in as sysadmin. I next ran

select
m.definition
from sys.procedures p
left join sys.sql_modules m on p.object_id=m.object_id
where p.name in ('spPUpdateContactUserLockout', 'spPLINK_SYNC_CONTACT');

and got this (note I included another stored proc for comparison purposes) enter image description here

I've not seen this before. Ever. I didn't think one could create a stored procedure without defining a body, and there are several such procedures in multiple databases on this instance. At this point I'm concerned, but I'm not sure if I don't have to be or if I should be more concerned than I currently am.

This instance is SQL Server 2017 Standard x64 RTM-CU11, and I'm using SSMS 17.9. Any insight is appreciated!

6

Yes, this is possible, no body required. How it got this way? Your guess is as good as ours. It is easy to do but we can't possibly know whether this was human error or some kind of truncation from a script or tool.

CREATE PROCEDURE dbo.blat
AS
GO
EXEC dbo.blat;

Output:

Command(s) completed successfully.

Good to have source control to sort this kind of thing out.

If you're concerned, though, you should check your application and the rest of the procedures to see if anything calls this empty procedure and actually expects it to do anything. Error handling, unit tests, etc. may be passing simply because it isn't throwing an error.

  • We didn't have auditing turned on at the time, so there's no way to know how this happened. Thank you for the suggestions! I plan on meeting with the development supervisor to see about making appropriate changes to our dev process. – S M Nov 21 '18 at 7:25
  • @SM The default trace may yield clues if you get in there fast enough. See dba.stackexchange.com/q/48052/1186 – Aaron Bertrand Nov 21 '18 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.