none
Dynamically generating code from a configuration table

    Question

  • Hi All.

    I am creating an audit specification which is I intend to make it flexible, for example add audit items to it using a configuration table and a stored procedure.

    I avoided making this a free text stored parameter, this is because of spelling mistakes etc, so I entered the values in a configuration table.

    So the table would have the following values as an example

    ID Audit_Description
    1 DATABASE_PRINCIPAL_CHANGE_GROUP
    2 DATABASE_PRINCIPAL_IMPERSONATION_GROUP
    3 DATABASE_ROLE_MEMBER_CHANGE_GROUP
    4 DBCC_GROUP
    5 FAILED_DATABASE_AUTHENTICATION_GROUP
    6 FAILED_LOGIN_GROUP
    7 LOGIN_CHANGE_PASSWORD_GROUP
    8 LOGOUT_GROUP
    9 SCHEMA_OBJECT_ACCESS_GROUP

    Now, I need to have a stored procedure that can add items to the specification below and also one to remove items.

    CREATE SERVER AUDIT SPECIFICATION [Specification_X]
    FOR SERVER AUDIT [Audit_X]
    ADD (SCHEMA_OBJECT_ACCESS_GROUP),
    ADD (DBCC_GROUP),
    ADD (LOGOUT_GROUP)

    In the example above, I have selected items which equate to ID 9,4,8

    The last item in the list should not have a comma,  the proc would accept audit ID, probably in a comma delimited form and then loop through to add the items as per the ID, if its only 1 ID provided, no comma, if its more than one, then the last item would omit the ID. If the audit item already exists, then it should be ignored.

    I am writing this for SQL 2008 to SQL 2017, so mindful that new SQL features may not work.

    Any help would be appreciated.

    DBCC_GROUP
    LOGOUT_GROUP
    LOGOUT_GROUP
    Monday, April 22, 2019 7:41 PM

All replies

  • Hi MrFlinstone,

    Thanks for your question.

    Per your description, I know that you want to generate code automatically for solving the problem of spelling mistakes. However, there is no need to generate code based on a configuration table. Also, T-SQL is not the only way to create SERVER AUDIT SPECIFICATION. You could achieve it by using the tool SSMS. Please refer:

    SQL Server auditing with Server and Database audit specifications

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 23, 2019 5:51 AM
    Moderator
  • Thanks for the reply, the solution has to be automated and as such cannot use SSMS GUI to create it.

    Tuesday, April 23, 2019 7:33 AM
  • Thanks for the reply, the solution has to be automated and as such cannot use SSMS GUI to create it.

    No matter what reason you matter, I feel that this is not a good option to achieve it. Since you insist on generating code based on audit item table, this function could let you start your task.

    CREATE TABLE V_Configuration
    (
     ID int,
     Audit_Description varchar(64)
    )
    
    insert into V_Configuration values
    (1,'DATABASE_PRINCIPAL_CHANGE_GROUP'), 
    (2,'DATABASE_PRINCIPAL_IMPERSONATION_GROUP'), 
    (3,'DATABASE_ROLE_MEMBER_CHANGE_GROUP'),  
    (4,'DBCC_GROUP'),  
    (5,'FAILED_DATABASE_AUTHENTICATION_GROUP'),  
    (6,'FAILED_LOGIN_GROUP'),  
    (7,'LOGIN_CHANGE_PASSWORD_GROUP'),  
    (8,'LOGOUT_GROUP'),  
    (9,'SCHEMA_OBJECT_ACCESS_GROUP')
    
    CREATE FUNCTION fn_generate_audit
    (
     @specification_name varchar(64),
     @server_audit_name varchar(64),
     @audit_items varchar(128),
     @delimiter CHAR(1) 
    )
    RETURNS VARCHAR(max)
    AS
    BEGIN
         declare @v_generate_code varchar(max)
    	 declare @split_table as table (id int)
    	 declare @combine_items varchar(max)
    
    	 DECLARE @start INT, @end INT 
         SELECT @start = 1, @end = CHARINDEX(@delimiter, @audit_items) 
         WHILE @start < LEN(@audit_items) + 1 
    	 BEGIN 
            IF @end = 0  
                SET @end = LEN(@audit_items) + 1
           
            INSERT INTO @split_table (id)  
            VALUES(CAST(SUBSTRING(@audit_items, @start, @end - @start) AS INT)) 
            SET @start = @end + 1 
            SET @end = CHARINDEX(@delimiter, @audit_items, @start)
    	 END
    
    	 SET @combine_items=STUFF((SELECT 
    	 ','+' ADD ('+T.Audit_Description+')'
    	 FROM V_Configuration T
    	 INNER JOIN @split_table T1 ON T.ID=T1.id FOR XML PATH('')),1,1,'')
    	 
    
    	 SET @v_generate_code='CREATE SERVER AUDIT SPECIFICATION '+QUOTENAME(@specification_name)+CHAR(13)+
    	 'FOR SERVER AUDIT '+QUOTENAME(@server_audit_name)+
    	 @combine_items
    
    	 RETURN @v_generate_code
    END
    
    SELECT dbo.fn_generate_audit('Specification_X','Audit_X','9,4,8',',')
    
    
    /*
    CREATE SERVER AUDIT SPECIFICATION [Specification_X]
     FOR SERVER AUDIT [Audit_X]
     ADD (SCHEMA_OBJECT_ACCESS_GROUP),
     ADD (DBCC_GROUP),
     ADD (LOGOUT_GROUP)
     */

    Best Regards,

    Will

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 7:07 AM
    Moderator
  • Thanks for the reply.

    Is there a reason you say that its not a good solution, my view was that if one has a large estate with loads of SQL instances, I want to be able to have the flexibility to add/remove audit items dynamically using stored procedures.

    Wednesday, April 24, 2019 11:54 AM
  • Audits should be fixed and unchanging.  They are very light. 

    This causes and auditing nightmare if you allow things to be turned on/off.  You would never know in an actual audit if it was on or not.

    What is your reason behind turning things on and off?

    Wednesday, April 24, 2019 1:24 PM
    Moderator
  • So the reason is this

    At the moment, there is lack of clarity with what needs to be audited.

    I am sure once there is clarity, it would not change. The idea is to write something that is flexible enough such that once they have more clarity, they can add the audit items.

    The alternative to hardcode is

    And when there is clarity, hard-coding it again and deploy everywhere.

    Wednesday, April 24, 2019 1:53 PM
  • I suggest you hard code it for everything you "might" want and leave it alone.  The audit has very little overhead or impact to the system.

    I audit every server for every object/user change and it amounts to a 2000-3000 rows a day.

    Wednesday, April 24, 2019 5:28 PM
    Moderator
  •  I will consider this, when you do your audit    do you  import the data into tables or leave this within files ?
    Thursday, April 25, 2019 4:53 PM
  • I have an SSIS process which pulls the audit every night through this view into a common repository for reporting.

    /*
    	Creates UserAudit and view in DBMaint
    
    	SEARCH AND REPLACE {SQLDIR} with actual SQL Directory, ie M:\Apps\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Audit\
    	AND CREATE DIRECTORY BEFORE RUNNING SCRIPT
    
    	REPLACE [DBAMaint] with an existing database name to create the audit view
    */
    
    USE [master]
    GO
    CREATE SERVER AUDIT [UserServerAudit]
    TO FILE 
    (	FILEPATH = N'{SQLDIR}'
    	,MAXSIZE = 1024 MB
    	,MAX_ROLLOVER_FILES = 10
    	,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	QUEUE_DELAY = 5000
    	,ON_FAILURE = CONTINUE
    	,AUDIT_GUID = '5ec3b99f-2336-45d2-846d-fe8ae1891cbc'
    )
    ALTER SERVER AUDIT [UserServerAudit] WITH (STATE = ON)
    GO
    CREATE SERVER AUDIT SPECIFICATION [UserServerAuditSpecification]
    FOR SERVER AUDIT [UserServerAudit]
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
    WITH (STATE = ON)
    GO
    
    USE [DBAMaint]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE VIEW [dbo].[UserAudit_vw]
    AS
    SELECT
    	[EventDateLocal] = DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),aud.event_time), 
    	aud.server_instance_name,
    	ActionName = CASE WHEN act.action_id IS NULL THEN act2.name ELSE act.[name] END,
    	cm.class_type_desc,
    	aud.database_name,
    	aud.schema_name,
    	aud.object_name,
    	aud.statement,
    	additional_information = CAST(aud.additional_information AS XML),
    	aud.session_server_principal_name,
    	aud.server_principal_name,
    	aud.database_principal_name,
    	aud.target_server_principal_name,
    	aud.target_database_principal_name,
    	aud.file_name,
    	aud.audit_file_offset,
    	aud.sequence_number,
    	aud.succeeded,
    	aud.session_id
    	--,aud.*
    FROM sys.fn_get_audit_file ('{SQLDIR}\UserServerAudit*',default,default) aud
    	INNER JOIN sys.dm_audit_class_type_map cm
    		ON cm.class_type = aud.class_type
    	LEFT OUTER JOIN sys.dm_audit_actions act
    		ON act.action_id = aud.action_id
    			AND act.class_desc = cm.securable_class_desc
    	LEFT OUTER JOIN sys.dm_audit_actions act2
    		ON act2.action_id = aud.action_id
    			AND act2.class_desc = cm.class_type_desc
    
    WHERE aud.class_type <> 'A'
    	AND aud.server_principal_name NOT IN ('sa')  -- Ignore service account and maintenance commands
    	AND NOT (aud.class_type = 'U' AND aud.action_id = 'AL' AND aud.[statement] LIKE 'TRUNCATE TABLE %') -- Ignore TRUNCATE TABLE reported as alter table audits
    	AND NOT (aud.class_type IN ('U','V') AND aud.action_id = 'AL' AND aud.[statement] LIKE 'ALTER INDEX %' AND aud.[statement] LIKE '%REBUILD%') -- Ignore index rebuild reported as alter table audits
    GO
    


    Thursday, April 25, 2019 5:01 PM
    Moderator