Marking a stored procedure as system object in SQL Server

Once my friend ask to me, It is possible to mark a stored procedure as system object allows to run in user database context?

I said yes we can do. We have to follow two-step which is mention below.

Step 1- The stored procedure name must begin with "sp_":

Create a stored procedure in the Master database which name begins with sp_

CREATE PROCEDURE sp_NewObject

@tableNM varchar(100)

AS

BEGIN

       IF EXISTS(SELECT  *

       FROM        sys.objects

       WHERE      name=@tableNM

       )

       BEGIN

              SELECT 'true' [Exist]

       END

       ELSE

       BEGIN

              SELECT 'false' [Exist]

       END

END

GO

Note: A stored procedure created with "sp_" prefix can be used in any user database without specifying database/schema. But, the procedure still runs in the context of the master database and not the user database. Let’s create a procedure to test this:

Step 2- The stored procedure must be marked as a system object explicitly:

You can mark a stored procedure as a system object using sys.sp_MS_marksystemobject system procedure.

Below code will mark the procedure as a system object:

USE [master]

GO

EXEC sys.sp_MS_marksystemobject sp_NewObject

GO

 

You can verify if the object is marked as a system object:

USE   [master]

SELECT      name, is_ms_shipped

FROM        sys.objects

WHERE       name = 'sp_NewObject'

result:

name                                    is_ms_shipped

----------------------------------------------------

sp_NewObject                 1

sp_NewObject is now marked as system object and can be run in user database context:

USE   [Your database name]

GO

EXEC  sp_NewObject 'Tbl_Name'

GO