DBMS application user roles should not be assigned unauthorized privileges.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15128	DG0105-SQLServer9	SV-24098r2_rule	DCFA-1	Medium

Description
Unauthorized access to the data can lead to loss of confidentiality and integrity of the data.

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

Details

Check Text ( C-23688r2_chk )
Compare privileges assigned to database application user roles to those defined in the System Security Plan. From the query prompt: SELECT name FROM [master].sys.databases WHERE name NOT IN ('tempdb', 'ReportServerTempDB') AND state = 0 Repeat for each database: USE [database name] SELECT r.name, o.name, p.permission_name FROM sys.database_principals r, sys.database_permissions p, sys.all_objects o WHERE p.grantee_principal_id = r.principal_id AND p.major_id = o.object_id AND r.principal_id NOT IN (0, 2) AND r.type IN ('A', 'R') AND r.is_fixed_role = 0 ORDER BY r.name, o.name, p.permission_name If the assigned privileges do not match the authorized list of privileges, this is a Finding. Note: Default privileges assigned to fixed data roles are considered authorized by default.

Check Text ( C-23688r2_chk )

Compare privileges assigned to database application user roles to those defined in the System Security Plan.

From the query prompt:

SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('tempdb', 'ReportServerTempDB')
AND state = 0

Repeat for each database:

USE [database name]
SELECT r.name, o.name, p.permission_name
FROM sys.database_principals r, sys.database_permissions p, sys.all_objects o
WHERE p.grantee_principal_id = r.principal_id
AND p.major_id = o.object_id
AND r.principal_id NOT IN (0, 2)
AND r.type IN ('A', 'R')
AND r.is_fixed_role = 0
ORDER BY r.name, o.name, p.permission_name

If the assigned privileges do not match the authorized list of privileges, this is a Finding.

Note: Default privileges assigned to fixed data roles are considered authorized by default.

Fix Text (F-23528r1_fix)
Use the grant and revoke commands to assign the authorized privileges as listed in the System Security Plan to custom database application or application user roles.