[RESOLVED] Prevent query execution in wrong Db

[cory_jackson]

I would like to add a safeguard to stop me from accidently executing a query on a Db. I have two Dbs on the same instance, one for development and one for production. I sometimes use Transaction but I can get careless and leave the Commit line in. Is there another way to ensure the Db name is that of the development Db? I was thinking of something that would check the Db name and abort is not desired.

[jdelano]

In what application are you doing these queries? I guess you're using SSMS?
I used this back in the day; https://www.mssqltips.com/sqlservert...n-environment/

[TysonLPrice]

I would like to add a safeguard to stop me from accidently executing a query on a Db. I have two Dbs on the same instance, one for development and one for production. I sometimes use Transaction but I can get careless and leave the Commit line in. Is there another way to ensure the Db name is that of the development Db? I was thinking of something that would check the Db name and abort is not desired.

Other than being careful I don't. You could set up DEV and PROD ID and just use the appropriate one. That could prevent "accidents".

[techgnome]

have two Dbs on the same instance, one for development and one for production.

That's the problem... they should at a minimum be in two separate instances. Even better would be that they are physically separated on two servers each with their own instances. I would then setup multiple users in Prod... one as a read-only user... the other as an admin... only run the admin when you're absolutely sure you're ready to run a destructive command. Use Read-Only for everything else. In the dev, db... it's the dev db, you should be able to handle it's loss and re-create it at any time. Also Prod should be backed up on a regular basis.

That said, these are all lessons learned the hard way.... I once dropped the Accts Rvcbl table once from a prod db... fortunately there was a backup that we could use to restore the dropped table from... So yeah, I feel yah... and now I'm paranoid, run everything in transactions and double, tripple check which connection I'm using before running updates or deletes.


-tg

[cory_jackson]

Color assignment is a good idea and I do that. Still I manage to miss it on rare occasion when hurrying.
Seperate servers: This is what the client had. It's on Azure so I don't know how to create separate instances. And even when I have in the past, I have two instances of SSMS. There's only two people who use the main Db and only I use the Dev.
I found a command to detect the Db name... I suppose I could stick my script in a Case statement. With the 'else' being "Select 'You're in the wrong Db Dummy!'" :-)

[cory_jackson]

If  Db_Name() <> 'Dev' Set NoExec on

Select 'My query goes here'

Set NoExec off
If  Db_Name() <> 'Dev' Select 'Wrong Db dummy!'

I found a trick in another forum to enable NoExec conditionally and modified it into this. It seems to work and I'll try i for a bit. I like it better than Transaction as if I am in the correct Db, there's nothing to modify or manually run like the "Commit". If you can see any pitfalls with this, please let me know.

[TysonLPrice]

That's the problem... they should at a minimum be in two separate instances. Even better would be that they are physically separated on two servers each with their own instances. I would then setup multiple users in Prod... one as a read-only user... the other as an admin... only run the admin when you're absolutely sure you're ready to run a destructive command. Use Read-Only for everything else. In the dev, db... it's the dev db, you should be able to handle it's loss and re-create it at any time. Also Prod should be backed up on a regular basis.

That said, these are all lessons learned the hard way.... I once dropped the Accts Rvcbl table once from a prod db... fortunately there was a backup that we could use to restore the dropped table from... So yeah, I feel yah... and now I'm paranoid, run everything in transactions and double, tripple check which connection I'm using before running updates or deletes.


-tg

Back in my mainframe years we used what we called a "Hot ID" with a shared password for production maintenance. If you screwed it up you were never given the password again From that point forward you had to ask someone else with access to do maintenance for you. You never wanted to be in that club.