Is it possible to create a global stored procedure at Sql server level

Question

I created a query that takes a database backup at certain specified location. I want to use it as a stored procedure but this should act as a global stored procedure so that whenever this SP is called. Then database backup is taken.

It uses DB_Name() to take database backup of owner database.

Is it possible to create any such SP or Function.

I am using sql server 2005

Answer 1

Three steps must be followed to create a "system" stored procedure that is accessible to all databases on the Server, as well as be able to run under the context of the current database when it is called.

1. Master Database - The stored procedure should be created in the Master database
2. Prefix Stored Procedure - The stored procedure name should be prefixed with sp_
3. Mark SP as System Object - Call sp_ms_marksystemobject to mark custom SP as a system object

Example Code Below

--Step 1, Create in master database
USE master
GO

--Step 2, Prefix with sp_ the custom proc
CREATE PROCEDURE sp_myCustomSystemProc
AS
BEGIN
   PRINT 'myCustomCode'
END
GO

--Step 3, Mark as system object so proc executes in context of current db
EXEC sp_ms_marksystemobject 'sp_myCustomSystemProc'
GO