HEELPBOOK - Marking a stored procedure as System Object (SQL Server) ####################

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:

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

 
The procedure sp_Objects runs under [master] even after switching the database using "USE DB". 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'


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

 
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!



############ ARTICLE INFO #############

Article Month: December
Article Date: 13/12/2012
Permalink: http://heelpbook.altervista.org/2012/marking-a-stored-procedure-as-system-object-sql-server/
Source: http://sqlandme.com/2011/10/03/sql-server-marking-a-stored-procedure-as-system-object/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts