System Stored Procedures that aren't

Question

Post reply

System Stored Procedures that aren't

TimothyAWiseman

SSCrazy Eights

Points: 8819
More actions
December 12, 2007 at 9:30 am

#184758

This is mostly a matter of curiosity, but when I work we routinely place stored procedures that will be used from or by multiple databases in the server's Master database. Sometimes they are placed normally with other user stored procedures and sometimes they are moved to the system stored procedures folder without any rhyme or reason I have yet been able to discern. Does anyone know why this happens and if there might be a way to stop it?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

off the top of my head do you make some of them system procs when you create them? ( using sp_ms_marksystemobject )

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

No, I always use create stored procedure Name

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

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

no you use this system proc call afterwards to make a user proc a system proc.

methinks you maybe shouldn't be messing with things you don't understand - otherwise you'd have understood my ref to the system proc.

have a read through BOL regarding the use of the master database and why some procs need to be marked as a system proc and why you'd want to do this.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Actually, it's a common problem... take a look at what you have for a "default catalog" when you login... credits to Navy beans says it's "Master".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 5

I know what sp_ms_marksystemobject does. Maybe it would have been more clear if I had said I only use create procedure and that is all. Some of them are getting placed as system stored procedures in spite of my not knowingly doing anything to set them that way.

I really don't know why you would want to set one as a system procedure. I tried taking your advice and looking it up in books online, but I could not find anything on the topic. sp_ms_marksystemobject does not have its own listing and the listing for the master database provides nothing new on the topic. I would appreciate it if you had a good reference on that topic.

As to messing with things you don't understand, how else are you going to come to understand them? Math is not a spectator sport, and neither is SQL.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 6

Jeff Moden (12/15/2007)
Actually, it's a common problem... take a look at what you have for a "default catalog" when you login... credits to Navy beans says it's "Master".

That it is. So why does that assign some of them to system and not others?

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Heh... dunno... must be a new "feature" than MS built in... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

Ah, of course, you have to love all of the new features. 😉

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

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

sorry but best practice says that you should always fully qualify object creation. If objects end up in wrong databases then the correct processes are not in place - for production systems this should be critical.

An object cannot get randomly marked as a system proc, although you can happily place objects in master.

The reasoning behind system procs is the scope for server wide commands , for instance I have a custom script which will rebuild database indexes, you can call it from any database and it will work, like sp_updatestats, however unless I mark it a system proc it doesn't work ( it worked ok in 2000 without being a system proc, but that's another matter ) I can think of 3rd party apps that add procs to master database , not sure if any mark them as system objects though.

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

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

oh sorry I meant to apologise if I had sounded contrite - but I stand by my comment, if you have a prod server with user procs in master you should know why they're there, and if you don't then it's probably very unwise to make any changes, the mark system proc is documented somewhere, maybe by Ken Henderson.

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 11

They were fully qualified, and they are going into the correct database. They were meant to be stored in master per the company standard policy, and they were. They also execute properly with exec master.dbo.procname, but in SSMS they appear in the listing of system procedures instead of the list of user procedures.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/