December 31, 2007 at 4:17 am
Experts,
I want to create system stored procedure in my database. is it possible ?
I have dbo permission in the database.
Say for example, i have to create a sp called sp_whodo in the user database.
Inputs are higly appreciable.
karthik
December 31, 2007 at 4:22 am
As far as I know you cannot create any system stored procedures in SQL Server, though you can create your own stored procedures and name them with a 'sp_' prefix. So if you create one such as this, and call it; it would be first searched for in the master database.
They say it is not a good practice to create such stored procedures, especially in the master database, due to security considerations.
December 31, 2007 at 4:47 am
But i heard it is possible. do you have any idea ?
karthik
December 31, 2007 at 7:19 am
It is possible to mark a procedure as a system procedure. It requires updating the system tables.
Modifying the system tables is not a good idea. It is a very bad practice that is recommended against by most experts. It's also almost guaranteed to loose you any support from MS that you might otherwise have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 31, 2007 at 7:32 am
Yes, it is possible to create a "system" stored procedure, and yes, it's almost always a bad idea.
What problem are you trying to solve by creating one? Most unusual solutions have alternate, standard solutions, that will do as well or better. If you let us know why you want to create a "system" stored procedure, we might be able to help you reach a more standard solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 31, 2007 at 7:41 am
GilaMonster (12/31/2007)
It requires updating the system tables.Modifying the system tables is not a good idea. It is a very bad practice that is recommended against by most experts. It's also almost guaranteed to loose you any support from MS that you might otherwise have.
In fact you don't have to edit the system tables, just use sp_MS_marksystemobject. Yes this procedure is undocumented, but that doesn't mean that you will loose support from MS. In fact the first time I used it was because as KB article mentioned it as a solution.
Another question is it if you really need to create a system object for what you want to achieve. Apart from the case in the KB which was about system object for replication which were created wrongly, I used it a couple of times for procedures which I want to call from any database, but execute within the context of the current database.
While using the sp_ prefix will take care of the first requirement, you need to mark it as system object fotr the second requirement.
[font="Verdana"]Markus Bohse[/font]
January 8, 2008 at 1:02 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply