UCF STIG Viewer Logo

DDL permissions should be granted only to authorized accounts.


Finding ID Version Rule ID IA Controls Severity
V-2463 DM1760-SQLServer9 SV-23804r2_rule ECLP-1 Medium
Data Definition Language (DDL) commands include CREATE, ALTER, and DROP object actions. These actions cause changes to the structure, definition and configuration of the DBMS as well as to the objects themselves that can affect any or all operations of the database. Such privileged actions, when not restricted to authorized persons and activities, can lead to a compromise of data and DBMS availability.
Microsoft SQL Server 2005 Database Security Technical Implementation Guide 2015-06-16


Check Text ( C-22835r2_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 ('G', 'W')
AND (p.type LIKE 'CR%' OR p.type LIKE 'AL%')
ORDER BY u.name, o.name, p.permission_name

Compare the results listed to those documented in the System Security Plan. If any accounts listed are:

1. Application users
2. Application user roles
3. Application administrator roles
4. PUBLIC role
5. GUEST user

this is a Finding.

If any application developer accounts are listed with DDL privileges to production databases, this is a Finding.

If results are not documented in the System Security Plan or authorized by the IAO, this is a Finding.
Fix Text (F-14854r1_fix)
Revoke DDL permissions from unauthorized accounts with the REVOKE command:

From the query prompt:

USE [database name]
REVOKE [permission] FROM [user name]

Document required DDL permission grants in the System Security Plan and authorize with the IAO.