June 3, 2004 at 7:47 am
I've seen much documentation from Microsoft about not creating user stored procedures in the master database. I assume the reason for this is security and recoverablity of stored procedures. No one wants to recover the master database to get a previous version of a stored procedure.
So I create them in another database just for Administrator stuff unless they just have to be in master.
Many sample user stored procedures from Microsoft and the SQL Server Community still put them in Master. Why is that? Am I just wrong about Microsoft's recommendations?
David Bird
June 5, 2004 at 9:21 am
I think more than anything else - it's a matter of control and good discipline. "Best Practice" standards will tell you to stay the hell out of master databases, and you'll want to keep any users out of there as well. So if you have any stored procs or other custom/admin gear that you've written, put it into a separate database.
I get really peeved when I see new objects in the master database, firstly because I want to know why a user has permissions to create create objects in master, and secondly, why are they tinkering around there in the first place? *grrrrrrrrrr*
June 6, 2004 at 1:54 pm
If there are any procedures in the master database, they should be items that need to run against all databases for maintenance, etc. This should be kept to a minimum or eliminated where possible though. As far as users creating objects in master....if that happens, you have a gaping security hole that needs patched up.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
June 7, 2004 at 6:40 am
User's are unable to create anything in Master. When I use the term user stored procedures, I'm referrering to stored procedures other than System stored procedures.
I just don't get why so many sample procedures are named sp_ which should be placed the MASTER database. Maybe Yukon will have another system database that will contain non-system stored procedures.
I am also anxious to tryout Microsoft's Best Practice Analysis tool and see how well I guessed at implementing their recommendations.
David Bird
June 7, 2004 at 3:46 pm
David,
when you say you create your procedures in another database you don't mean Production database and Master database. If I understand you correctly and there is yet another database with SPs then do not forget about SQL2KSP3 (or 3A) for that matter that introduced a checkbox in server and database properties for CrossDatabase Ownership Chaining.
I am sure that you took care of your server and you create your objects connected as an sa ( DBO). But a lot of developers like to connect using Windows authentication or generic standard login that is one for the whole group. Well, whatever they create do not have a dbo as an owner.
Taken together both things that I mentioned create a lot of problems when working between databases. I would add to your advice that if you create SPs in the second database check Allow CrossDatabase Ownership Chaining on applicable databases.
Yelena
Regards,Yelena Varsha
June 8, 2004 at 4:17 am
Yelena,
What you are asking is not hard. What you can do (for example), is create roles for your users/developers in the second database, which give them *execute* permissions on the stored procedures called from the first database - a 'RunStoredProc' role in effect... then they can call stored procs from their own databases eg - master.dbo.xp_sendmail...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply