Managing security for SQL restores

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