Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

This appears to be a system procedure..but it isn’t!

G’day,

Recently, I was browsing through my database server when suddenly under the “System Stored Procedures” folder of the master database I seen a stored procedure that I knew had been written at my workplace. I knew this stored procedure never shipped with SQL SERVER 2008 and had not been placed there by any service pack or hot fix. It had definitely been written in house.

But what was it doing in the system objects on the master database. I first though something strange was going on with my database, but a quick visit to dr google, soon brought me to another conclusion.

I ran the query.

SELECT * FROM sys.objects where is_ms_shipped = 1

Which quickly brought up the name of my rouge stored procedure in the list of system objects.

I initially thought that somebody had been playing with the system tables – something that I’m sure PSS would not be too impressed with should I ever have to call them with an issue on this particular server.

But then I hit on the reason (at least I hope this is the reason!) that the procedure was “marked” as a system object.

There is an undocumented stored procedure called “sp_MS_marksystemobject”, that simple takes one argument, and that’s the name of the object that needs to be marked as a system object.

I am not sure of the pros and cons of taking such action, but if you read the comments within the “sp_MS_marksystemobject” stored procedure

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_MS_marksystemobject'));

then you’ll see that the procedure is only intended for internal microsoft use.

So, now, what I intended to do was to “un-mark” the procedure as a system stored procedure.

I looked for another stored procedure to do this, but could not find one.

I thought about attempting to update any system tables to “un-mark” this as a system stored procedure, however I discounted that.

So, I asked on twitter using #sqlhelp tag – and very quckly got a response back.

In the end I simply dropped the object. After all, I knew what it did and I was happy that dropping it would have no consequences. Of course I checked first if there were any inter-dependencies or specific security attached to the object – but there were not. I then re-created it as a normal object.

I’m not sure why I didn’t just think of doing this in the first place, I think it was because I assumed that I would not be able to drop an object that was marked as a system object.

This whole situation, left me with a few questions.

Supposing an object has been marked as a system object – how do you un-mark it (without altering the system tables)? I mean what if the object had inter-dependencies?

and

What are the advantages \ disadvantages of marking any stored procedure (or any object for that matter) as a system object.

But then again, I guess that is one of the reasons that the procedure “sp_MS_marksystemobject” is undocumented in the first place.

I’d be keen to hear any feedback \ suggestion on what you would have done in this situation or the wisdom of using this stored procedure in the first place.

Have a good day

cheers

Martin.

Comments

Posted by Steve Jones on 6 April 2011

I think there is an issue here, and I would like to see this proc removed from SQL Server.

I'd have altered the proc, however, to add logging to a table with login tracking and set a job to alert me when it ran so that I could track down the offender / offending code.

Posted by Martin Catherall on 6 April 2011

I like the idea of setting a little trap to catch the offender. I'll consider that as an option next time. I like the way you think :)

Posted by vanessa4biz on 16 February 2014

This sounds like a bad thing. Well, I found a blog that suggest that I use this when executing a procedure so that it can be accessed from any database. I need to create a script to search all databases within an instance, except for system table, for fragmentation. This blog was the only one I found that had an answer as to how I can create a stored procedure in the master folder to search all databases. Is there a another option to using sp_MS_marksystemobject?

Leave a Comment

Please register or log in to leave a comment.