This shows you the differences between two versions of the page.

Link to this comparison view

tsql:creating_system_stored_procedures [2017/09/23 12:10] (current)
richard created
Line 1: Line 1:
 +====== Creating system stored procedures ======
 +[[https://​docs.microsoft.com/​en-us/​sql/​relational-databases/​system-stored-procedures/​system-stored-procedures-transact-sql|System stored procedures]] (along with other types of system object) appear logically in the ''​sys''​ schema of all other databases. ​
 +To make a user-defined SP available in this way:
 +  -- Create the SP in the ''​master''​ database'​s ''​dbo''​ schema, with a name beginning with ''​sp_''​... (e.g. ''​sp_MySystemSp''​).
 +  -- Grant access to it for all users:
 +  .. <code tsql>​GRANT EXEC ON sp_MySystemSp TO public</​code>​
 +  -- Mark it as a system stored procedure:
 +  .. <code tsql>​EXEC sys.sp_MS_marksystemobject '​sp_MySystemSp'</​code>​
 +{{tag> tsql admin development}}