So while at last week’s SQL PASS Summit 2011, during the dinner on Friday night we had a discussion about adding stored procedures to the Master database and being able to refer to it anywhere in your SQL Server instance. This allowed for direct reference without a three part naming format, IE [database].[dbo].[procedure_name]
The discussion was primarily around whether or not you had to mark the stored procedure as a ‘system procedure’. Well, turns out that you don’t. However, after a little research I found out that by marking it as a ‘system procedure’, it will allow the procedure to run in the context of the database you are currently in versus running within the Master database, specifically when dealing with system objects.