November 14, 2013 at 5:57 pm
Mauve (11/14/2013)
I assign all of my SQL Server logins to the tempdb as the default database. NEVER master. This way no harm can be done by an application that just connects to the server without specifying a database name as part of the connection string. Ideally, I'd like to see an option of "none".
This is the same thing that I've been doing for years. Primarily because of the number of times I've executed a build script, when the the database it was trying to go to in the initial USE command didn't exist. Then I got to spend the next few hours deleting hundreds of objects from my now polluted [master] database.
However, if I start from [tempdb] and the USE [..] fails, it dumps everything there instead and I can easily clear it by just re-starting the SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 15, 2013 at 7:13 am
Whenever I sign onto to a development database to run DDL Scripts I not only put a Use statement in my script but I also use the drop down to select the database I intend to work with before doing anything (or select a database in the object explorer before opening a query window. It is just a habit that I've always had. Probably from when I was learning SQL Server back in the nineties and you had to do that.
In Test, Staging and Production, I don't tend to worry as much, because I have no rights to master and couldn't do anything there if I tried, and anyway I am only supposed to prepare the scripts for the customer's representive to run.
I've also found that if I don't put a "GO" after my "use" statement, then if the database doesn't exist, nothing else happens.
November 15, 2013 at 9:46 am
RBarryYoung (11/14/2013)
Primarily because of the number of times I've executed a build script, when the the database it was trying to go to in the initial USE command didn't exist. Then I got to spend the next few hours deleting hundreds of objects from my now polluted [master] database.However, if I start from [tempdb] and the USE [..] fails, it dumps everything there instead and I can easily clear it by just re-starting the SQL Server.
Yep - master getting cluttered up is protected by (at least) three controls/processes/habits:
1) Default database tempdb except for (human) sysadmin accounts. Defeated by human mistakes.
2) Don't give rights to Master except to sysadmin accounts. Defeated in part by (third party) apps that require access to, say, master.dbo.xp_cmdshell, and in whole by (third party) apps that end up with sysadmin.
3) In scripts you write/alter yourself, put USE tempdb; USE YourDB; at the top, so if YourDB doesn't exist, it's already pointed at tempdb. Defeated by scripts you don't write/alter, such as the automated build scripts mentioned above.
November 21, 2013 at 4:07 am
We have always left master as default, as no users have any permissions there, and so far no sysadmin has done any harm; but I see the point, and perhaps we should consider using tempdb instead. We do have a few applications that are using JDBC and have no editable connection string for specifying the database, so we have to set the default as the relevant application database. And we recently discovered a live app that had set its default as the demo version of its database - so it stopped when we dropped the demo (unusual; but easily remedied).
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply