Jason Shadonix


Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it

Thanks,

Jason




Re: All my stored procedures are getting created as system procedures!

Louis Davidson


What evidence do you have that they are system stored procedures And what would it hurt anyhow

I would never suggest that you should put procedures in the master database anyhow, I always use a seperate database that is mine to own.







Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

I agree putting stuff in master is odd, but I don't have much choice (long story).

How do I know If you query sys.procedures, there is a is_ms_shipped field set to 1. (I think it's actually from sys.objects and sys.procedures gets it from there, but won't swear to that).

Besides that, it shows up in the '\programmability\stored procedures\system stored procedures\' folder instead of the '\programmability\stored procedures' folder.

As for what it hurts...you have to be a sysadmin to execute system stored procedures. I need non sysadmins to be able to execute some of them.





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

OK...I've decided to look at this backwards to see if it helps.

Now I'm looking for an article/KB/post that tells you how to create a system stored procedure. Perhaps an explanation of how to make a stored procedure a system stored procedure will also tell you how to undo that.

I know in SQL2000 you could mark an SP as system after it was created, but from what I'm seeing you can't do that in 2005 (or at least it's harder to find out how to). Does anyone know of anywhere I can find that information

Thanks,

Jason





Re: All my stored procedures are getting created as system procedures!

Rohit Nayak

Jason,

I tried the following against master database on Sql Server 2005

use master

go

--Create a stored proc

create proc dbo.testProc

@inStr varchar(20)

as

begin

print @inStr

end

--Execute the stored proc that was created

exec dbo.testProc 'Test'

go

drop proc dbo.testProc

The proc got created correctly under 'Stored Procedures' folder and not the 'System Stored Procedures' folder

Only thing that I can think of is that you need to check whether 'Dbo' owns the proc or not - since all the procs under the 'System Stored Procedures' folder are owned by 'sys'

Hope this helps.

~Rohit





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

Rohit,

I've got almost 100 SQL servers, mixture of 2000 and 2005. All but one of them behave like you say above, just one is putting all sps as system stored procedures. I don't think anyone will be able to reproduce this unless you happen to be having the same problem (whatever the problem is...)

I've played arround with it, and it doesn't matter what schema I create them in, they all become system stored procedures. (Is it even possible to create a procedure, system or otherwise, in the .sys schema I think it will give you an error if you try it). I've also tried different users with differenet permissions. Doesn't make a difference.

Example:

CREATE PROCEDURE dbo.bob

as

print 'bob'

go

select *

from

sys.procedures p

inner join sys.schemas s

on p.schema_id = s.schema_id

where

p.name = 'bob'

go

EXECUTE dbo.bob

go

DROP PROCEDURE dbo.bob

---

This creates and runs the SP just fine. The schema shows up as .dbo in the query, and the is_ms_shipped field is set to 1. You also have to be a sysadmin to be able to run the procedure. If it weren't for that, I probably would have never noticed this problem was even there.





Re: All my stored procedures are getting created as system procedures!

Tom Phillips

Rohit,

We have seen that symptom also. Everything we create in master gets put into the "System Stored Procedures". Even stranger, is it only happens on 2 of our 14 servers. We have never figured out why, but it didn't hurt anything, so we live with it.





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

I've finally found someone else that has the same problem.

I'm not sure if it makes me feel better or not, but at least I'm not alone...




Re: All my stored procedures are getting created as system procedures!

hunchback

I haven't been able to reproduce the behavior on 2005, but anyway I am going to give it a try. Can you check the status of the trace flag 1717. This trace flag was used on 2000 to mark objects as system objects.

DBCC TRACESTATUS (1717)

AMB





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

I executed the DBCC TRACESTATUS (1717) and get back all zeros (not enabled).

Basked on BOL, that trace flag doesn't exist in 2005 though. (Though there is so much undocumented stuff that I guess that doesn't really mean anything).





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

I also just figured out if I create a UDF in master, it gets created as a regular user function instead of a system function.





Re: All my stored procedures are getting created as system procedures!

Louis Davidson

I am asking around to see if anyone knows about this, and the question came up to check the allow updates system configuration:

sp_configure 'allow updates'

What does this return If it is 1 for the run_value, try:

sp_configure 'allow updates', 0

go

reconfigure

If not, we can try again, or you might have to escalate to product support...






Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

It is 1 (and appears to be 0 in other servers), so that may be the difference in configuration.

But then the question is, why would allowing updates cause this behavior Also, according to the books online for the allow updates option:

"This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported."





Re: All my stored procedures are getting created as system procedures!

Jason Shadonix

I stand corrected. Making that change fixes it. Very strange.

Now I wonder how it got set that way in the first place. I tried to duplicate it on a test server, and it won't even let you set that to 1 unless you do reconfigure with override.

In any case, it's working properly again.

Thanks!

Louis Davidson wrote:

I am asking around to see if anyone knows about this, and the question came up to check the allow updates system configuration:

sp_configure 'allow updates'

What does this return If it is 1 for the run_value, try:

sp_configure 'allow updates', 0

go

reconfigure

If not, we can try again, or you might have to escalate to product support...





Re: All my stored procedures are getting created as system procedures!

Louis Davidson

Excellent. I don't know either. Any chance it was like that from an upgrade

Either way, Umachandar was the one who gave me the answer. His answers always rock.