UCF STIG Viewer Logo

Permissions using the WITH GRANT OPTION should be granted only to DBA or application administrator accounts.


Finding ID Version Rule ID IA Controls Severity
V-2498 DM5144-SQLServer9 SV-23833r2_rule ECLP-1 Medium
The WITH GRANT option assigned with privileges, allows the grantee of the privilege to re-grant the privilege to other accounts. Unauthorized or unmanaged assignment of privileges may result in a compromise of data confidentiality and database operation. Privilege assignment should be restricted to DBA, application object owner accounts and application administration accounts.
Microsoft SQL Server 2005 Database Security Technical Implementation Guide 2015-06-16


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

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

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], o.name AS [Object], p.permission_name AS [Permission], p.state_desc AS [State]
FROM sys.database_principals u
JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
LEFT JOIN sys.all_objects o ON p.major_id = o.object_id AND p.class IN (1, 8)
LEFT JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class = 3
WHERE p.state_desc <> 'DENY'
AND p.state IN ('W')
ORDER BY u.name, o.name, p.permission_name

Review listed object grants. Compare with the list documented in the System Security Plan and authorized by the IAO.

If any object grants listed are not documented in the System Security Plan and authorized by the IAO, this is a Finding.
Fix Text (F-2479r1_fix)
Revoke unauthorized permissions granted with the WITH GRANT option.

From the query prompt:

USE [Database Name]

You may re-grant the object permissions to the Principal as shown below.

From the query prompt:

USE [Database Name]
GRANT [permission] ON [object] TO [Principal]

Document required object permission grants with Grant Option in the System Security Plan and authorize with the IAO.