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'