August 28, 2013 at 10:21 am
Hi need a help ?
Mistakenly restored the prod database instance instead of development and i am having full privelage(sysadmin) on the server. can we have any procedure/trigger/server level permission to restrict this kind of situation to happen again in production. So when ever they required to do a refresh to prod we will grant/alter the permission to do.
August 28, 2013 at 10:23 am
You cannot deny anything to a sysadmin.
You could try a DDL trigger on CREATE DATABASE, but the problem with those is they are AFTER triggers and I'm not sure if they work for RESTORE database as well as CREATE. I suspect not because it can't be rolled back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2013 at 10:25 am
by any way we can kill the command if it get fired to a production server.
we will not deny restore database, but can we kill the restore before database go to restoring state.
August 28, 2013 at 10:31 am
No, because as soon as the restore has started the files it's using get written over. You can kill it at any point, but the database being restored over will already be gone at that time and you'd need to restart a restore to get the DB back
Maybe take away his sysadmin permissions, limit to other permissions to do what he needs and stress the importance of being really, really careful. Or make the normal login that the guys use a non-sysadmin so that they have to change login to do anything sensitive
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2013 at 10:48 am
any other way or any one else can put your suggestions for the same.......
Thnaks for the reply Gila.
August 28, 2013 at 11:56 am
You cannot prevent yourself from restoring a production database at any point in the future by any other method then double-checking your work before you hit the button.
Sysadmin will ALWAYS be able to restore. If you don't want that ability, take away Sysadmin and give yourself other server roles except for the Backup role.
Think of it this way. To SQL Server, a sysadmin is God. Therefore there is nothing you can do to prevent God from doing whatever (s)he likes.
You should be proud of yourself. You have just taught yourself a valuable lesson that will stay with you the rest of your career. If I understand the situation correctly, you only had to teach it to yourself once. Some people make the same mistakes over and over again without learning from them.
August 28, 2013 at 12:32 pm
I periodically have to restore to test instances and while its certainly not foolproof, the first time I do this, I script all the restore and update T-SQL out into a text file and paste into SSMS from there. This way, I have a reference and only had to type it in correctly once. Plus, psychologically, it just seems to be reassuring to me.
I also really really like the idea as mentioned of having separate logins and this used to be the norm for the sort of work I used to do, but nowaday's getting Windows administrators to even consider this seems to be a lost cause, and from what I've read, many SQL Server folks are probably of the same mindset.
August 28, 2013 at 12:55 pm
das.saroj09 (8/28/2013)
Hi need a help ?Mistakenly restored the prod database instance instead of development and i am having full privelage(sysadmin) on the server. can we have any procedure/trigger/server level permission to restrict this kind of situation to happen again in production. So when ever they required to do a refresh to prod we will grant/alter the permission to do.
This is a way to help avoid this... and failed security audits. The backups for Dev and the backups for Prod should be on separate secure shares and the Dev SQLServer login should NOT have the privs to see the Prod backups directly and vice versa.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2013 at 1:42 pm
I believe you can put in a server level trigger, but this should not be relied upon.
By the way, situations like this is one reason some folks put development in a separate AD domain and ensure that the development credentials don't have access to production, just as Jeff said. If they are in a separate forest and production does not trust development and there is no forest-level trust, you ensure that if you are using your development account there is NO way of crossing that barrier.
K. Brian Kelley
@kbriankelley
August 28, 2013 at 2:33 pm
One more vote for separate access and accounts in dev and production. It won't prevent it, but it can stop lots of silly mistakes.
August 28, 2013 at 6:49 pm
Here's another vote for multiple logins.
In addition, another vote for taking greater care and caution when attempting to do a restore.
The server trigger may work, but I agree with Brian that it shouldn't be relied upon.
I checked throughout PBM as well and there is no means there to prevent the restore either (not without preventing the user to login).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 28, 2013 at 8:14 pm
Another vote for separate account for Dev and Production.
The separate shares for the backups is also good for this.
In some ways, there is some safety in having to request another person who normally does not work in the environment to do this.
Yes, it can slow things down a bit. But it's probably well worth avoiding the mistake.
August 29, 2013 at 4:39 am
The main issue with having separate accounts and instances for Dev and Prod is that for one-stop shops like the one I work it, a sysadmin can still have multiple windows of SSMS open and can still restore Dev over Prod if (s)he is not paying attention.
That's one of the reasons I color code my instances' query windows in SSMS (green for Dev, yellow for Test, orange for QC, and red for Production). The colors help me remember what environment I'm currently connected to. Also, I stop before I hit any okay buttons and verify everything in the windows to make sure I'm pointing to the correct instance.
In the case of a restore, we have different drive letters for all our instances, so it makes it easy to see at a glance if we're restoring to the correct drive letter. If I'm restoring to D: instead of G: for instance, I know I've hit the wrong instance before I even click OK.
August 29, 2013 at 6:54 am
A great product for ensuring the proper color coding based on server name, for instance, is Mladen's SSMS Tools Pack. You can set the color and how big the visual cue is. Works wonderfully. I rely on it heavily in my environments. I have production coded as red. That stands out.
http://www.ssmstoolspack.com/]SSMS Tools Pack
K. Brian Kelley
@kbriankelley
August 29, 2013 at 6:59 am
K. Brian Kelley (8/29/2013)
A great product for ensuring the proper color coding based on server name, for instance, is Mladen's SSMS Tools Pack.
I just use the regular OPTIONS available with native SSMS. But then, it only color codes the bottom of the window and can be reset to default if something strange happens to SSMS.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply