Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

How to deny restore database in sql server to SA? Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 6, Visits: 99
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.
Post #1489312
Posted Wednesday, August 28, 2013 10:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1489313
Posted Wednesday, August 28, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 6, Visits: 99
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.
Post #1489315
Posted Wednesday, August 28, 2013 10:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1489316
Posted Wednesday, August 28, 2013 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 6, Visits: 99
any other way or any one else can put your suggestions for the same.......

Thnaks for the reply Gila.
Post #1489327
Posted Wednesday, August 28, 2013 11:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:14 AM
Points: 7,197, Visits: 6,341
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1489359
Posted Wednesday, August 28, 2013 12:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 411, Visits: 2,408
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.
Post #1489373
Posted Wednesday, August 28, 2013 12:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 PM
Points: 36,995, Visits: 31,514
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489383
Posted Wednesday, August 28, 2013 1:42 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1489399
Posted Wednesday, August 28, 2013 2:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:56 PM
Points: 33,202, Visits: 15,348
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1489420
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse