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
      


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...