DBMS privileges to restore database data or other DBMS configurations, features or objects should be restricted to authorized DBMS accounts.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15107	DG0063-SQLServer9	SV-24084r2_rule	ECLP-1	Medium

Description
Unauthorized restoration of database data, objects, or other configuration or features can result in a loss of data integrity, unauthorized configuration, or other DBMS interruption or compromise.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-23507r2_chk )
Review DBMS roles and accounts granted the CREATE DATABASE permission, sysadmin or dbcreator fixed server roles, and the member of each database db_owner role: 1. Accounts granted CREATE DATABASE permission or DBCREATOR server role. From the query prompt: SELECT p.name 'User', r.name 'Role' FROM [master].sys.server_principals p, [master].sys.server_principals r, [master].sys.server_role_members m WHERE p.principal_id = m.member_principal_id AND r.principal_id = m.role_principal_id AND m.role_principal_id = 9 AND m.member_principal_id <> 1 ORDER BY r.name, p.name 2. Accounts granted SYSADMIN permission or SYSADMIN server role. From the query prompt: SELECT p.name 'User', r.name 'Role' FROM [master].sys.server_principals p, [master].sys.server_principals r, [master].sys.server_role_members m WHERE p.principal_id = m.member_principal_id AND r.principal_id = m.role_principal_id AND m.role_principal_id = 3 AND m.member_principal_id <> 1 ORDER BY r.name, p.name 3. Accounts granted CREATE DATABASE permissions or granted DB_OWNER database role. From the query prompt: SELECT name FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [database name] SELECT p.name 'User', r.name 'Role' FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m WHERE p.principal_id = m.member_principal_id AND r.principal_id = m.role_principal_id AND m.role_principal_id = 16384 ORDER BY r.name, p.name If any are not authorized for RESTORE permissions, this is a Finding. The 'sa' account (SID = 0x01) and the database owner account are authorized accounts. These accounts do not require explicit authorization and do not count as a Finding.

Check Text ( C-23507r2_chk )

Review DBMS roles and accounts granted the CREATE DATABASE permission, sysadmin or dbcreator fixed server roles, and the member of each database db_owner role:

1. Accounts granted CREATE DATABASE permission or DBCREATOR server role.

From the query prompt:

SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r, [master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 9
AND m.member_principal_id <> 1
ORDER BY r.name, p.name

2. Accounts granted SYSADMIN permission or SYSADMIN server role.

From the query prompt:

SELECT p.name 'User', r.name 'Role'
FROM [master].sys.server_principals p, [master].sys.server_principals r, [master].sys.server_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 3
AND m.member_principal_id <> 1
ORDER BY r.name, p.name

3. Accounts granted CREATE DATABASE permissions or granted DB_OWNER database role.

From the query prompt:

SELECT name
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [database name]
SELECT p.name 'User', r.name 'Role'
FROM sys.database_principals p, sys.database_principals r, sys.database_role_members m
WHERE p.principal_id = m.member_principal_id
AND r.principal_id = m.role_principal_id
AND m.role_principal_id = 16384
ORDER BY r.name, p.name

If any are not authorized for RESTORE permissions, this is a Finding.

The 'sa' account (SID = 0x01) and the database owner account are authorized accounts. These accounts do not require explicit authorization and do not count as a Finding.

Fix Text (F-24468r1_fix)
Define DBMS roles that are authorized for database restore functions, restrict assignment of restore privileges to those roles, and assign those roles only to authorized DBMS accounts.