SQL Server Prevent Scripts Being Run on the Wrong Instance...

Question

This is less of a programming question and more of a suggestions for tools one :-)

I'm looking for a way to prevent scripts being accidentally run on the wrong sql server instance, ie so you don't run that script for clearing down customers in your development environment on the live/production environment.

What I'm basically after is a pluging for Enterprise Manager (or a different app for managing a sql server instance), that would allow me to define a list of connection details for SQL server instances, so that when the window is connected to a LIVE environment that it makes it quite clear, either by coloring the tab red, or by poping up a message box saying "This is connected to XXXX instance. Are you sure"

Asside form being really careful, does anyone have any ideas for tools/plugins that will do this or anything remotely simular?

Thanks for any help

Answer 1

You can set different (limited) access right on you live instance for the user you normally use. And then have a different user name for doing any serious stuff on the live system.

Answer 2

All my scripts start with this :

if exists (select 1 from dbo.environment where value in('production', 'qa'))
  return

Of course this means that I have an environment table and have 'production' , 'development' , 'qa' etc. in it depending on where it is hosted.

Answer 3

This SQL code checks if you're on the right server:

if @@servername <> 'testserver'
  RAISERROR ( 'Wrong server', 16, 1 );

An error (or return for that matter), will prevent execution until the next GO statement. For example, the following will still print hello world, and delete the user table:

RAISERROR ( 'Wrong server', 16, 1 );
go
drop table users
print 'Hello world!'
go

Answer 4

here you go: http://www.ssmstoolspack.com/

this free SSMS addin provides windows coloring per server.