Results 1 to 7 of 7

Thread: [RESOLVED] Prevent query execution in wrong Db

  1. #1

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,114

    Resolved [RESOLVED] Prevent query execution in wrong Db

    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.

  2. #2
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    376

    Re: Prevent query execution in wrong Db

    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/

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,898

    Re: Prevent query execution in wrong Db

    Quote Originally Posted by cory_jackson View Post
    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".
    Please remember next time...elections matter!

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,669

    Re: Prevent query execution in wrong Db

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,114

    Re: Prevent query execution in wrong Db

    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!'" :-)

  6. #6

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,114

    Re: Prevent query execution in wrong Db

    Code:
    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.

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,898

    Re: Prevent query execution in wrong Db

    Quote Originally Posted by techgnome View Post
    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.
    Last edited by TysonLPrice; Aug 26th, 2024 at 06:49 AM.
    Please remember next time...elections matter!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width