Managing security for SQL restores

07 March,2016 by Jack Vamvas

Security controls for SQL Server restores and backups are critical to maintaining data integrity. Let's look at a scenario to highlight the potential dangers of not having checks in place.

A request is placed to restore a database to the Restore team. It is scheduled to occur at 02:00. The target server is  myserverT1  . During the restore process, the operator accidentally types myserverP1. The database replaces a database but not on the server intended. No one notices for a few days, edits are made to the data .

This is a very simplistic example, and slightly extreme ,but you get the idea! By the way , I have seen this occur with dramatic results.

In a high volume database environment security controls and processes are essential to minimise mistakes. There are a number of areas to focus on when building a process for SQL Server database restores.

1) Capturing the correct information of the database to restore and to the target server. What process do you have in place which documents the server\database details ? Are those documents double checked by the DBA team.

2) Is there a dedicated list of authorised users who can complete the Restores? Are security audits in place to identify who has Restore privileges?

3) When the restore completes - is there a handback to authorised DBA who can doublecheck the correct database has been restored to the intended server?

There are a number of methods such as : LSN numbers, script changes


4)Are you auditing regularly database security?

Read More on security audit

SQL Server Security Policy

Powershell sql server security audit - SQL Server DBA

SQL Server - Database Server Security Audit Process - SQL Server ...

SQL Server - Display restore history for a single database


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Managing security for SQL restores


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer