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.
You need to create the stored procedure on master database (the one you wish to mark as a system object).
sp_ms_marksystemobject is an undocumented Microsoft supplied procedure to register an object with the engine.
EXEC master.sys.sp_MS_marksystemobject 'sp_helptable' -- Object Name
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.
Grant permission to a particular user or group or to the public,
GRANT EXEC ON sp_helptable TO public --Granting the execute permission to public so that all user can access it