I recently wanted to create a stored procedure that would be only in master database and get some information_schema stuff for the current database.
One requirement is that the sproc has s "sp_" prefix. I also thought that it's the only requirement. I was wrong not for the first nor the last time.
The sproc would always return me information schema data belonging to the master db.
The second thing you have to do is mark the sproc a system object.
In Sql Server 2000 we have to set the system marking to on with master.dbo.sp_MS_upd_sysobj_category
In Sql Server 2005 we have the sys.sp_MS_marksystemobject stored procedure that does the trick.
This is how it's done:
SQL Server 2000:
USE master
-- This turns the SS2k's system marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
go
CREATE PROC sp_TestMasterAccess
AS
SELECT *
FROM information_schema.tables
-- This turns the SS2k's system marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
EXEC sp_TestMasterAccess
go
USE northwind
EXEC sp_TestMasterAccess
go
USE master
DROP PROC sp_TestMasterAccess
SQL Server 2005:
USE master
go
CREATE PROC sp_TestMasterAccess
AS
SELECT *
FROM information_schema.tables
GO
-- SS2k5 provides a stored procedure to mark the object as system
EXEC sys.sp_MS_marksystemobject sp_TestMasterAccess
GO
EXEC sp_TestMasterAccess
go
USE AdventureWorks
EXEC sp_TestMasterAccess
go
USE master
DROP PROC sp_TestMasterAccess
Enjoy!