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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
Categories: SQLServer
  1. Rumesh Srivastav
    December 23, 2011 at 2:44 pm

    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
    February 10, 2012 at 12:32 am

    Nice Article..thanks

  3. February 13, 2012 at 1:04 pm

    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?

    • February 23, 2012 at 7:33 pm

      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.

  4. debbie
    May 2, 2012 at 7:50 pm

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

  5. Neel
    June 14, 2012 at 3:34 pm

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: