secretGeek.netDifferentiating between environments within SQL Server Management Studio
When you're looking at a query window that's connected to Production, it would be nice if it was visually distinct from a non-production environment.
For example, there could be a red theme applied to the window.
Furthermore, there could be a warning before a potentially destructive query is executed. (Essentially, any query that isn't just a select - for example, a drop, delete, update, insert, create or alter)
This would be an effective way to lower the chance of something being done to production that was intended for another environment (pre-prod say, or development)
I can imagine a straw-man counter-argument to this concept that says:
"The only people who should have access to production are people who know what they're doing"
Or alternatively,
"Developers shouldn't have access to production"
And that's fine by me.
We can limit who accesses production, and we can give minimum privileges to those that do have access.
But even then:
*Someone* will have destructive privileges in production.
And that person will definitely have access to more than one system.
And that person, no matter how smart or conscientious they are, will be fallible.
Hence, it's helpful for that person to be able to differentiate (visually) between the different environments.
Why am I writing this blog entry right now?
Don't worry. I stopped myself in time ;-)
(And like all blog posts everywhere, there is already a stackoverflow question that says the same thing and a whole lot more)
Next → ← PreviousMy book "Choose Your First Product" is available now.
It gives you 4 easy steps to find and validate a humble product idea.
Michael D. Hall on March 23, 2009 09:50 sez:
As a counterpoint to your preemptive counterpoint, I'd like to point out that there are shops that have one or two developers that are also responsible for production. Just as a safe-guard for "those who know better" it could save their skin, more than once I was bouncing between dev and prod running queries trying to figure out some problem and accidentally ran a query in production that I *meant* to run on development. I'm bad. It could become the UAC of Sql development. :-(
Should I mark time spent on secretGeek as productive in TimeSnapper?
Michael L Perry on March 23, 2009 13:20 sez:
"Shit!!" should be the default, not "OK".
Todd on March 23, 2009 13:40 sez:
There is nothing wrong will allowing developers access to production, just make the separate tasks and make it more difficult to access that system so you can't do this accidentally. You can separate production into a different domain, or create a specific production login. Either way a developer's main login shouldn't be allowed to access production systems.
The unix crowd learned this long ago: create a separate user and administrator account. Your user account shouldn't be an administrator.
Mladen on March 23, 2009 13:59 sez:
Hi!
if you're looking for the bright red thinige for your production servers then you might want to check out www.ssmstoolspack.com
It's a free add-in for ssms that offers the functionality you wish.
i had the same problem as you. :))
Wedge on March 23, 2009 14:06 sez:
This is a good reason to use completely different db servers for testing and production. It makes these sorts of problems a fair bit more difficult.
Also, it's a shame that DBs aren't more like source control (with a change history and the ability to revert changes), even though they easily could be.
lb on March 23, 2009 15:02 sez:
@Mladen
>check out www.ssmstoolspack.com
Excellent work, Mladen. Just the thing!
It's got a tonne of nice features. Brilliant stuff.
Tuftesque on March 23, 2009 17:48 sez:
The use of <font color='red'> is very Tuftesque.
Steven Nagy on March 24, 2009 03:48 sez:
I really couldn't be assed (yes its a word) reading ALL the comments, so I'm just gonna make my view point, which by the way, is the correct view point.
First, as pointed out, this idea isn't new. Even now sys admins get a red font when they are running command prompt with their admin account.
The key point here is that there are 2 accounts. No one should ever accidentally do something in prod. Your developer account has read access to prod and write access locally. Then you have a developer admin account that has master privileges. This account usually has a password of length int.max for security. If you REALLY need to be doing something in prod, you use the special account. You only log in long enough to do your special work, then log out again.
This is how it should be done. The infrastructure world has had this nailed for years. You're not dumber than a sys admin are you Leon?
Chad Miller on March 29, 2009 09:14 sez:
In 2008 version of SQL Server Management Stduio you can color code your connection without any add-ins. The feature is natively part of the SSMS 2008. Go To Registered Servers >> Right-Click a server >> Connection Properties tab >> Select "Use Custom Color"
The feature is not natively available in SSMS 2005, however SSMS 2008 is backward compatible.
rudyx on March 30, 2009 12:09 sez:
I personally use different backgrounds for wondows via RDP:
light red - production
light yellow - test
light blue - development
light brown - sand boxes
I do wish SSMS could as well
Jim Butts on April 06, 2009 12:10 sez:
Steven Nagy Hit the nail on the head. There absolutely has to be account seperation. Color code whatever you like but make sure that the account for dev has no connection to the production account. A pint I'd like to add is that a common mistake is to give 1 "master" accout out. Each production account should be personal. I disagree with the idea that there is nothing wrong with developers geting access to production. Developers in particular should be exclusive to a dev or test environment. Allowing them access to production can quickly lead to an undocumented production environment as acess encourages both management and developers to just "fix" a problem without doing proper testing. As far as the "Someone" with destructive privleges, if it's not a developer, that someone is less likely to go poking around a production system.
Anthony on May 13, 2009 14:37 sez:
Wont work for me. Colorblindness sort of hinders that sort of thing.
lb on May 13, 2009 15:16 sez:
@Anthony
Yes indeed, it would need to be configurable.
Chad Miller points out (above) that this feature is available in SSMS 2008. I expect they've made it configurable enough to handle for colour blindness.
Anthony on May 13, 2009 19:10 sez:
Cool! I will have to give that a shot when I install it.
Tony Z on September 14, 2011 19:39 sez:
Feature in SSMS 2008 to color code connections is completely worthless. Worse than non-existent. You have to use a specific method to actually connect and have it do the color, and then if you switch connections it doesn't update the color! Doh!.
lb on July 06, 2012 03:17 sez:
SQL Server 2008 R2 has this built in nicely.
When creating a new connection click on:
"Options >>" -- then go to the
"Connection properties" tab
"[ ] use custom color "
"Select ..."
and make production BLOOD RED.