Home > SQLServer > SQL Server – Marking a stored procedure as system object

SQL Server – Marking a stored procedure as system object


Marking a stored procedure as system object allows to run the procedure in a user database context. There are two requirements to allow running a procedure created in [master] database in user database context:

 

1. The stored procedure name must begin with "sp_" :

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

USE   [master]

GO

 

CREATE PROCEDURE sp_Objects

AS

SELECT      name, object_id, type_desc

FROM        sys.objects

WHERE       is_ms_shipped <> 1

GO

 

— Execute procedure in [master]

SELECT DB_NAME() 'Current Database'

EXEC sp_Objects

 

— Execute procedure in [SqlAndMe]

USE   [SqlAndMe]

 

SELECT DB_NAME() 'Current Database'

 

EXEC sp_Objects

Result Set:

Current Database

——————–

master

 

(1 row(s) affected)

 

name                 object_id            type_desc

————–       —————-     ——————–

sp_who_blocked       1291151645           SQL_STORED_PROCEDURE

sp_Objects           1531152500           SQL_STORED_PROCEDURE

 

(2 row(s) affected)

 

Current Database

——————–

SqlAndMe

 

(1 row(s) affected)

 

name                 object_id            type_desc

————–       —————-     ——————–

sp_who_blocked       1291151645           SQL_STORED_PROCEDURE

sp_Objects           1531152500           SQL_STORED_PROCEDURE

 

(2 row(s) affected)

As you can see from the result set, the procedure sp_Objects runs under [master] even after switching the database using "USE DB".

 

2. The stored procedure must be marked as system object explicitly:

You can mark a stored procedure as system object using sys.sp_MS_marksystemobject system procedure. Let’s mark our procedure sp_Objects as system object and re-execute above code.

Below code will mark the procedure as system object:

USE [master]

 

EXEC sys.sp_MS_marksystemobject sp_Objects

 

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

USE   [master]

 

SELECT      name, is_ms_shipped

FROM        sys.objects

WHERE       name = 'sp_objects'

Result Set:

name          is_ms_shipped

———–   ————–

sp_Objects    1

 

(1 row(s) affected)

 

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

— Execute procedure in [master]

USE   [master]

SELECT DB_NAME() 'Current Database'

EXEC  sp_Objects

 

— Execute procedure in [SqlAndMe]

USE   [SqlAndMe]

SELECT DB_NAME() 'Current Database'

EXEC  sp_Objects

Result Set:

Current Database

——————–

master

 

(1 row(s) affected)

 

name                 object_id            type_desc

————–       —————-     ——————–

sp_who_blocked       1291151645           SQL_STORED_PROCEDURE

 

(1 row(s) affected)

 

Current Database

——————–

SqlAndMe

 

(1 row(s) affected)

 

name                 object_id            type_desc

————–       —————-     ——————–

LastNames            21575115             USER_TABLE

Customer             62623266             USER_TABLE

Employees            165575628            USER_TABLE

 

(64 row(s) affected)

 

 

You can also create tables in master database which begin with prefix "sp_", and these can be used in user databases without database/schema prefix. It does not need to marked as system object. Try below example yourself:

— Create Table in [master]

USE   [master]

GO

SELECT DB_NAME() 'Current Database'

CREATE TABLE sp_Table1

(

      Col1 CHAR(10)

)

 

INSERT INTO sp_Table1

VALUES ('Master')

 

 

— Insert/Select from [SqlAndMe]

USE   [SqlAndMe]

SELECT DB_NAME() 'Current Database'

INSERT INTO sp_Table1

VALUES ('SqlAndMe')

 

SELECT      *

FROM  sp_Table1

 

 

Hope This Helps!

Vishal

  1. Rumesh Srivastav
    23.12.2011 02:44 | #1

    Very informative post. Its really helped me lot. Thanks for sharing with us. Check out this link too its also having wonderful explanation on stored procedure in sql server.

    http://mindstick.com/Articles/79be36ec-d4e6-4ef5-ae5d-c65fd7ede2b9/?Stored%20Procedure%20in%20SQL

    Thanks

  2. SQLBack
    10.02.2012 12:32 | #2

    Nice Article..thanks

  3. Sandeep Maher
    13.02.2012 01:04 | #3

    Hi,
    I have more than one customer so i have created Database for customer. And having SP same. I have added SP in Master database and accessing in different database. may i know is there any performance issue? or any other problem?

  4. Vishal
    23.02.2012 07:33 | #4

    based on your requirement I think that it is right way to go. There is no impact on performance. Another option is to create stored procedure in model database, it will be available on all new databases you create.

  5. debbie
    02.05.2012 07:50 | #5

    Thanks Vishal. This is exaclty what I needed to do and your post helped a lot.

  6. Neel
    14.06.2012 03:34 | #6

    Nice article but just thought of adding to it. If you do not want to make the SP as system proc, as sys.sp_MS_marksystemobject is undocumented, you can make the stored procedure to have dynamic SQL, which will make the SQL to run in the context of the database even if it is not a system proc.

    The above proc can be made like below:

    CREATE PROCEDURE sp_Objects
    AS
    DECLARE @SQL varchar(1000)
    SET @SQL = ‘SELECT name, object_id, type_desc
    FROM sys.objects
    WHERE is_ms_shipped 1’
    EXEC(@SQL)
    go

  1. No trackbacks yet.