System Stored Procedure Question

Question

Post reply

System Stored Procedure Question

TheRedneckDBA

SSChampion

Points: 14001
More actions
November 6, 2007 at 1:12 pm

#122192

I need to create a stored procedure in the master database (yes, I know it's not that good of an idea). I'm working with SQL 2K5, SP2
Whenever I create it, it is marked as a system stored procedure no matter what I name it.
Any idea why it's happening, and what to do about it?
I'm confused...we've got a couple dozen others in the master database, and have never had this trouble before.
Thanks,
Jason
The Redneck DBA

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

pretty sure this is what happened:

somebody ran this command:

--Turn system object marking on

EXEC master.dbo.sp_MS_upd_sysobj_category 1

after that is run, all commands that create objects in SQL 2000 are system objects. this is fine if you were adding your own stuff, like sp_who3 or something, but you are supposed to turn off system marking after you are done.

to stop that behavior run this command:

--Turn system object marking off

EXEC master.dbo.sp_MS_upd_sysobj_category 2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 2

TheRedneckDBA

SSChampion

Points: 14001

November 6, 2007 at 2:11 pm

#748864

Does this apply to SQL 2K5 also?

The Redneck DBA

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 3

Apparently not...when I tried it in 2K5 it said it doesn't exist. For what it's worth, I also went thru the microsoft support / chat we have access to and they weren't able to provide me with any info on this.

The Redneck DBA

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 4

OK, I've decided to approach this backwards.

Now I'm looking for a way to create a stored procedure (In SQL 2005, NOT 2000) and mark it as a system stored procedure. Hopefully if I can find information on that it will also tell me how to take a system stored procedure and reverse the process. This would be a temporary fix, as I eventually need to figure out why the procedures are getting set to system stored procedures in the first place, but at least it would be something.

Anyone have any ideas?

I know you could mark a user SP as a system SP in 2000, but I don't see anything on it for 2K5.

The Redneck DBA

Lowell SSC Guru Points: 323518 More actions · Answer 5

well i found that SQL2005 has a stored proc to mark a specific object as system, instead of an on/off switch, so I'm still looking:

-- SS2k5 provides a stored procedure to mark the object as system

EXEC sys.sp_MS_marksystemobject sp_Yourprocame_That_should_start_with_SpUnderscore

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 6

Thanks.

It's funny. It appears that Microsoft really doesn't want you to create stored procedures as system stored procedures. That proc. you mention isn't in BOL. But all of a sudden their product won't let me create anything but a system stored procedure 🙂

The Redneck DBA

Brandie Tarvin SSC Guru Points: 173034 More actions · Answer 7

Jason,

You've got me curious now. How are you seeing it marked as a system stored procedure? In what screen or from what query?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

I'm likewise confused as I make use of placing user procs in master named sp_ and unless I actually mark them as system objects they are not.

Just out of interest I mention that some ( user ) procs named sp_ placed in master that used to be accessible from all user databases in sql 2000 require making into system procs in sql 2005 to do the same thing.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

I am curious about how you are determining they are system procedures also.

The results returned by the two statements below will NOT return system procedures.

USE MASTER

GO

SELECT * FROM sys.objects WHERE NAME LIKE 'SP_%' AND sys.objects.is_ms_shipped = 0

SELECT * FROM sysobjects WHERE sysobjects.[name] LIKE 'SP_%' and category = 0

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 10

You are correct. Niether of those queries below will return system stored procedures.

I am verifying mine three ways:

1) SELECT * FROM sys.objects where is_ms_shipped = 1

2) SELECT * FROM sys.procedures where is_ms_shipped = 1

3) By looking. There is a special folder for system stored procedures in SSMS

One interesting thing is it's marking that is_ms_shipped flag even if the name doesn't start with sp_. I can name an sp BOB and it gets created as system.

Bob Fazio (11/8/2007)
I am curious about how you are determining they are system procedures also.
The results returned by the two statements below will NOT return system procedures.
USE MASTER
GO
SELECT * FROM sys.objects WHERE NAME LIKE 'SP_%' AND sys.objects.is_ms_shipped = 0
SELECT * FROM sysobjects WHERE sysobjects.[name] LIKE 'SP_%' and category = 0

The Redneck DBA

Brandie Tarvin SSC Guru Points: 173034 More actions · Answer 11

I take it you're creating these with the CREATE PROCEDURE statement instead of using a template or right-clicking the Stored Procedure folder and choosing new?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 12

Correct. But all that SSMS does when you right-click and say new is open a new query window with a CREATE PROCEDURE stubbed out for you. Would that really matter?

Brandie Tarvin (11/8/2007)
I take it you're creating these with the CREATE PROCEDURE statement instead of using a template or right-clicking the Stored Procedure folder and choosing new?

The Redneck DBA

Brandie Tarvin SSC Guru Points: 173034 More actions · Answer 13

I don't know, but it doesn't hurt to check and see if there is a difference. @=)

I'm going to do some playing around and see if I can replicate what's happening to you now.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 14

Thanks. I've tried to replicate the issue on other servers (we've got about 15 or so SQL 2K5 boxes) and haven't been able to. There has to be something stupid I'm missing on this one server, but I just can't figure out what it is.

The Redneck DBA