Thursday, November 3, 2011

Why all my stored procedures are saved in master database under System stored procedure folder?

Just having a discussion with a colleague , she made some changes in configuration and now when she creates a simple (not a system) stored procedure in master database it saves under Programmability --Stored Procedures--System Stored Procedures. Is it comfortable? No,right? After some investigation I found that we need to return 'allow updates' to 0 , see below script

EXEC sp_configure 'allow updates',0
reconfigure

Now everything got back to work in the 'right' place.

2 comments:

Shatrughna Kumar said...

Nice Info.
I was not aware about this.

Mordechai said...

I tested with this script, and the stored procedure was saved normally:

use master
EXEC sp_configure 'allow updates',1
reconfigure with override

create procedure usp_deleteme
AS
BEGIN
select top 10 * from sys.tables
END

select * from sys.procedures where name ='usp_deleteme'


EXEC sp_configure 'allow updates',0
reconfigure with override

in object explorer usp_deleteme did not appear under "system stored procedures"