How to create a system object in SQL Server – sp_ms_marksystemobject

All we know that the system objects are residing in master database and they can be called from any other databases available on that instance, with the respective context. That’s cool, having an object at one place and being called from other databases without actually having it? At some point of time, you will also want to have your own crafted stored procedures to act like a system object and accessible to other databases available on the instance.

Let’s mark our sp_helptable stored procedure as a system object. If you are wondering about sp_helptable procedure then please read the previous blog post here. Basically this is a hand crafted stored procedure not a Microsoft shipped procedure, developed to get us the table definition on query window  like the other system stored procedure sp_helptext for objects like functions, sps etc.

Step 01:
You need to create the stored procedure on master database (the one you wish to mark as a system object).

Step 02:
sp_ms_marksystemobject is an undocumented Microsoft supplied procedure to register an object with the engine.

Now you can see the sp under system object folder. Also the is_ms_shipped column is updated to 1 in sys.procedures, which means sp_helptable has been successfully registered as a system object.

Step 03:
Grant permission to a particular user or group or to the public,

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: