Application users privileges should be restricted to assignment using application user roles.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15629	DG0121-SQLServer9	SV-24106r2_rule	ECLP-1	Medium

Description
Privileges granted outside the role of the application user job function are more likely to go unmanaged or without oversight for authorization. Maintenance of privileges using roles defined for discrete job functions offers improved oversight of application user privilege assignments and helps to protect against unauthorized privilege assignment.

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

Details

Check Text ( C-23691r2_chk )
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 u.name, o.name, p.permission_name FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p WHERE o.object_id = p.major_id AND p.grantee_principal_id = u.principal_id AND p.state IN ('G', 'W') AND u.type IN ('S', 'U') ORDER BY u.name, o.name, p.permission_name If any names are listed, this is a Finding.

Check Text ( C-23691r2_chk )

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 u.name, o.name, p.permission_name
FROM sys.all_objects o, sys.database_principals u, sys.database_permissions p
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.state IN ('G', 'W')
AND u.type IN ('S', 'U')
ORDER BY u.name, o.name, p.permission_name

If any names are listed, this is a Finding.

Fix Text (F-23530r1_fix)
Revoke permissions assigned directly to user accounts and grant them instead to the appropriate group account. From the query prompt: REVOKE [permission] ON [object] FROM [user name] GRANT [permission] ON [object] TO [group name] Document any exceptions to privileges that cannot be assigned via database roles in the System Security Plan.