====== 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>