SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to deny restore database in sql server to SA?


How to deny restore database in sql server to SA?

Author
Message
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4170 Visits: 8593
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25799 Visits: 12494
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25799 Visits: 12494
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

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24534 Visits: 1917
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
@‌kbriankelley
das.saroj09
das.saroj09
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 276
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.
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37339 Visits: 9268
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search