SQL

-- STIG V-214001, V-214002
-- SQL Server must generate audit records when privileges/permissions are modified.
-- SQL Server must generate audit records when unsuccessful attempts to modify privileges/permissions occur.
-- Check V-214001 and V-214002 use the same logic, so this implementation combines them,
 
SET @IsFinding = 0
 
DECLARE @TblTmp TABLE (AuditName VARCHAR(100), SpecName VARCHAR(100), [ActionName] VARCHAR(100), RESULT VARCHAR(100))
 
IF NOT EXISTS(
SELECT name AS 'Audit Name',
status_desc AS 'Audit Status',
audit_file_path AS 'Current Audit File'
FROM sys.dm_server_audit_status
)
SET @IsFinding = 1
 
INSERT INTO @TblTmp (AuditName, SpecName, ActionName, RESULT)
SELECT a.name AS 'AuditName',
s.name AS 'SpecName',
d.audit_action_name AS 'ActionName',
d.audited_result AS 'Result'
FROM sys.server_audit_specifications s
JOIN sys.server_audits a ON s.audit_guid = a.audit_guid
JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id
WHERE a.is_state_enabled = 1
AND d.audit_action_name IN ('DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP'
,'DATABASE_OBJECT_PERMISSION_CHANGE_GROUP'
,'DATABASE_OWNERSHIP_CHANGE_GROUP'
,'DATABASE_PERMISSION_CHANGE_GROUP'
,'DATABASE_ROLE_MEMBER_CHANGE_GROUP'
,'SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP'
,'SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP'
,'SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP'
,'SERVER_OBJECT_PERMISSION_CHANGE_GROUP'
,'SERVER_PERMISSION_CHANGE_GROUP'
,'SERVER_ROLE_MEMBER_CHANGE_GROUP')
 
-- All 11 of the above referenced audit_action_name values
-- must be present to pass this check.
IF (SELECT COUNT(*) FROM @TblTmp) <> 11
SET @IsFinding = 1
 
IF @IsFinding = 1
SELECT 'Finding' AS STATUS, 'STIG V-214001' AS reference
UNION
SELECT 'Finding' AS STATUS, 'STIG V-214002' AS reference
ELSE
SELECT 'Pass' AS STATUS, 'STIG V-214001' AS reference
UNION
SELECT 'Pass' AS STATUS, 'STIG V-214002' AS reference