Differences

This shows you the differences between two versions of the page.

--- qnd:sql [2021/12/27 20:29] – created mgupton
+++ qnd:sql [2021/12/27 20:35] (current) – mgupton
@@ Line 1: / Line 1: @@
 ====== SQL ======
+=== Transact SQL Example ===
+<code 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
+</code>