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.