|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Wednesday, September 07, 2011 7:27 AM
Points: 8,313,
Visits: 554
|
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Monday, February 06, 2012 3:37 PM
Points: 2,617,
Visits: 1,057
|
|
Interesting
I've always insisted that stored procedures were prefixed with usp (user stored procedure) so that I can spot the ones for which I should expect documentation.
As there are an ever growing number of internal stored procedures it is vital that non-internal stored procedures are immediately identifiable, if only to show that you don't have to trawl for BOL to find out what they were supposed to do.
LinkedIn Profile
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Yesterday @ 1:46 AM
Points: 573,
Visits: 91
|
|
It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.
Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 07, 2012 5:16 AM
Points: 141,
Visits: 56
|
|
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.
Jon Reade
Jon
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, February 07, 2012 5:16 AM
Points: 141,
Visits: 56
|
|
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.
Jon Reade
Jon
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, September 07, 2011 8:38 AM
Points: 28,
Visits: 96
|
|
Well, my experience is absolutely the opposite of James Travis - and is exactly what I would have expected!
Here is what I did, using Sql Server 2000 Sp2 under Windows 2000 professional Sp3:
- Opened Query Analyzer
- Typed in the following:
use master
go
create procedure sp_mysp as
select 'I am the master'
go
use miscellaneous
go
exec miscellaneous..sp_mysp
go
create procedure sp_mysp as
select 'I am NOT the master'
go
exec miscellaneous..sp__mysp
and got exactly the same as James
I am the master
I am NOT the master
- I closed Sql Server 2000 (SP2) down, then restarted it
- Then
use miscellaneous
go
exec miscellaneous..sp_mysp
STILL gives
I am NOT the master
- And
sp__mysp
ALSO gives
I am NOT the master
- And
use Maintenance
go
exec sp_mysp
gives
I am the master
So how do we explain these 2 sets of opposing results?
|
|
|
|
|
Keeper of the Duck
Group: Moderators
Last Login: Wednesday, February 01, 2012 8:09 AM
Points: 6,524,
Visits: 1,698
|
|
Did you mark the stored procedure in the master database as a system object? I don't see that you did based on your explanation. That would explain the discrepancy based on both the original forum discussion and the article.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Site | Blog |  View Brian Kelley's LinkedIn profile | Twitter
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Wednesday, September 07, 2011 7:27 AM
Points: 8,313,
Visits: 554
|
|
I believe Brain is right. If you look back I mark the sp as a system object by running
sp_MS_marksystemobject
against it.
It is not a documented procedure but what it does is change the bitmask of the status of the object to match with system type. Problem is once done there is no real safe way to undo (can be done don't get me wrong) and as long as marked as a system type you cannot drop thru EM (sorry didn't try thru QA and wasn't really worried since is a test server I got to rebuild in the next few months anyway). Once you do that and restart the server, your results should match up.
|
|
|
|
|
SSCrazy Eights
Group: Moderators
Last Login: Wednesday, September 07, 2011 7:27 AM
Points: 8,313,
Visits: 554
|
|
quote:
It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.
Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Yes, that would be a great feature. This one may be a future possiblity thou if you look at what they did with QA in SQL 2000 and the schema view window (or whatever it is called).
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, September 07, 2011 8:38 AM
Points: 28,
Visits: 96
|
|
OK, having run sp_ms_marksystemobject, I get the same results as James.
But the article was entitled 'What is the search order for Procedures prefixed sp_?'
In that context, running sp_ms_marksystemobject is not relevant. And I would therefore argue that there is no reason why user-written stored procedures should not be named with the 'sp_' prefix. I would also suggest that having 2 stored procedures with the same name, where one of them resides within master, is poor management.
My own convention is to prefix any sps I write which I store into master with 'sp__' (that's 2 underscores instead of 1). So I get the benefit of a universally available sp, but at the same time can easily see which sps within master are 'mine'.
In other dbs, I use sprName for sps I expect to invoke directly (usually from code within my app), and spr_ for sps that are not invoked directly by me, but instead are invoked from within the (higher level) sprName sps.
And fortunately, I operate in an environment where I have complete and sole control over all such standards and conventions <g>!
|
|
|
|
