Creating system stored procedures

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:

  1. Create the SP in the master database's dbo schema, with a name beginning with sp_… (e.g. sp_MySystemSp).

  2. Grant access to it for all users: 

    GRANT EXEC ON sp_MySystemSp TO public

  3. Mark it as a system stored procedure: 

    EXEC sys.sp_MS_marksystemobject 'sp_MySystemSp'

, ,