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 Friday, August 30, 2013 12:25 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 672, Visits: 6,767
I think in most cases, it probably deserves to be up a level.

In many environments, you only promote objects to production.
So designing a fool proof process to avoid overwriting Prod with Dev is what should be the main discussion.
As long as the same person has sysadmin in both environments, there is potential to repeat.
Things like color can help, but will not stop fast fingers.
I like Jeff's approach, as you would have to copy the backup to another area to do it.
Similar to ours, where the deveolopers did not have access to those backups.
So you worked with someone else to get the restore done.
Post #1490260
Posted Friday, August 30, 2013 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
SQLRNNR (8/28/2013)
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).

You don't have to just prevent the development dba login, you must also forbid the production dba login access to the development backups, and forbid that access to the production SQL Service account and the production SQL Agent account, and also deny the development dba login write access to the production backups. Otherwise it's fairly easy to do the wrong thing.


Tom
Post #1490298
Posted Friday, August 30, 2013 2:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
Brandie Tarvin (8/29/2013)
K. Brian Kelley (8/29/2013)
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.


Unless the same person is doing both and happens to have different SSMS windows open. In which case, it doesn't matter what that person does to prevent these things from happening in the future, they still will happen unless that person double-checks and pays attention.

If the two logins are different NT logins (rather than SQL logins) two windows simultaneously doesn't matter provided the production NT login doesn't have any access to the development backup filestore and the development db login doesn't have write access to the production db backup filestore; it's a good idea to deny the production service and agent accounts access to development filestore too. You are of course right tat splitting the two functions using SQL Server logins rather than NT logins is not good protection against this sort of foul-up.


Tom
Post #1490300
Posted Friday, August 30, 2013 2:33 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:50 AM
Points: 6,624, Visits: 1,874
I wasn't speaking of SQL Server logins. If you go back up the thread, I was talking two Windows logins in different domains (in different forests) where there is no trust in the production domain of the non-production domain credentials.


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 #1490311
Posted Friday, August 30, 2013 10:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:40 AM
Points: 6, Visits: 113
Hi All,

The situation we faced is like, one request came to restore Prod backup to Dev server with last available backup, so we have checked the last backup file history through query analyzer on prod and we have have connected the Dev through SSMS on prod, while we have to run restore on dev env. Unfortunately we have selected a new query and it was opened from prod environment and we have stared the refresh on it, so we realized after we stared. in the mean time we have contacted the application for the same and updated with the situation, but if any data might have written to the database on prod, it will be difficult to find out, we have checked the SQL server default trace to find out the details for any object got created/dropped. is there any query to find out what might have changed/inserted/updated/truncated on production during these time and apart from color coding any other suggestion on the same.

i really thank all of the member who replied on the context, i like to know more on it, please do contribute your suggestion.

Post #1490345
Posted Tuesday, September 3, 2013 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
So you restored an older Prod backup over the current version of Prod?

This means none of the multiple login suggestions that were made will work. You can't prevent a sysadmin from restoring over a database with its own backup.

However, if you want to restore databases from production to Dev, do what we do in our environment. Build a job on the Dev database that pulls from the Production backup repository and restores. Then you have something that you don't have to rewrite each time the request is made and you don't have to worry about switching environments in SSMS because the job will only run against Dev. (Make sure there are no Linked Servers that could confuse the issue).


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 #1490962
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse