UCF STIG Viewer Logo

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


Finding ID Version Rule ID IA Controls Severity
V-15629 DG0121-SQLServer9 SV-24106r2_rule ECLP-1 Medium
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.
Microsoft SQL Server 2005 Database Security Technical Implementation Guide 2015-06-16


Check Text ( C-23691r2_chk )
From the query prompt:

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.