Top
Link Central
Tags
Posts
(somewhat) Social
« Enhance Your Skills For Free | Main | ourSSD 2 - Are My Databases Being Backed Up? »
Monday
Feb082010

DDL Auditing

DateMonday, February 8, 2010 at 1:00PM

This is posted as a sidebar to a great auditing post by Peter Schott: DDL Schema Change Auditing on SQL Server 2005 / 2008 which he posted in response to a tweet from @bensdad03

This is a slightly adjusted version to what I use currently, and will be forming a part of the ourSSD serires.

 

First we are going to create the audit specification. This is going to capture all the DDL changes on the server.

USE [master]

GO

 

CREATE SERVER AUDIT SPECIFICATION [ServerAudit_DDL_Changes]

FOR SERVER AUDIT [Server Audit DDL]

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SERVER_OBJECT_CHANGE_GROUP),

ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP)

WITH (STATE = ON)

GO

 

 

Next create the audit, this is going to a flat file out on the OS rather than into a SQL table.

USE [master]

GO

 

/****** Object:  Audit [Server Audit DDL]    Script Date: 02/08/2010 12:40:34 ******/

CREATE SERVER AUDIT [Server Audit DDL]

TO FILE

(      FILEPATH = N'I:\Audits\'

       ,MAXSIZE = 200 MB

       ,MAX_ROLLOVER_FILES = 2147483647

       ,RESERVE_DISK_SPACE = OFF

)

WITH

(      QUEUE_DELAY = 1000

       ,ON_FAILURE = CONTINUE

       ,AUDIT_GUID = '163ff4dd-f871-46e3-8158-d19d73eb9f75'

)

GO

 

 

 

Once you have the audit up and running it comes down to pulling the data into a table and doing what you want with it…

I have a couple of procs, one that loads the data from the log into tables (everything into one table then smaller tables that contain subsets for failed audits or for changes not related to certain automated processes running under the SQL Service account). Then another proc which will email out all attempted changes made during the last 36 hours along with the object and whether the change was successful.

 

 

/****** Object:  StoredProcedure [dbo].[Load_DDLAudit_Logs]    Script Date: 02/08/2010 12:41:44 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[Load_DDLAudit_Logs]

 

AS

 

/**********************************************

******TABLE CREATE STATEMENTS AT THE END*******

**********************************************/

 

SET NOCOUNT ON

 

DECLARE @AUDIT_FILE NVARCHAR(MAX)

DECLARE @LOGFILENAME NVARCHAR(500)

 

--Gets the current audit file name

SELECT @AUDIT_FILE =  audit_file_path from sys.dm_server_audit_status where name like '%DDL%'

 

--We are only going to load data from the current file

TRUNCATE TABLE DDL_Audit_Log_Temp

 

--Pull in the data from the audit file

INSERT INTO DDL_Audit_Log_Temp

SELECT *

FROM

              SYS.FN_GET_AUDIT_FILE(@AUDIT_FILE,DEFAULT, DEFAULT)   

             

 

 

--Get the max data from the full audit log table, this will be the only one we need

DECLARE @MAXDATE DATETIME

SELECT @MAXDATE = max(event_time) from DDL_Full_Audit_Log

IF @MAXDATE IS NULL

SELECT @MAXDATE = DATEADD(yy, -1, getdate())

 

 

--Load all data into the table holding ALL audit data (for back reference)

INSERT INTO DDL_Full_Audit_Log

SELECT * FROM DDL_Audit_Log_Temp

WHERE event_time > @MAXDATE

 

 

--Load all the data from users outside of the DBA team into the audit check table

INSERT INTO DDL_Audit_Check

SELECT * FROM DDL_Audit_Log_Temp

       WHERE SERVER_PRINCIPAL_NAME NOT IN (<LIST>)           

       AND event_time > @MAXDATE

      

      

--Load all the data for failed events into the failures table

INSERT INTO DDL_Audit_Failures

SELECT * FROM DDL_Audit_Log_Temp

       event_time > @MAXDATE

       AND succeeded = 0

      

 

--Load the data from users outside of the DBA team into the offload table which will be used by SSIS to pull data centrally

TRUNCATE TABLE DDL_Audit_Check_Offload  

 

INSERT INTO DDL_Audit_Check_Offload

SELECT * FROM DDL_Audit_Log_Temp

       WHERE SERVER_PRINCIPAL_NAME NOT IN (<LIST>)           

       AND event_time > @MAXDATE

      

 

 

 --All Audits - ddl_full_audit_log

 --Questionable Audits - DDL_Audit_Check

 --Failed Audits - DDL_Audit_Failures

 --Load audits temp - DDL_Audit_Log_Temp

 --select * from ddl_audit_check order by event_time desc

 

/*************** TABLE GENERATION **********************/

 

/*

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[DDL_Full_Audit_Log](

       [event_time] [datetime2](7) NOT NULL,

       [sequence_number] [int] NOT NULL,

       [action_id] [varchar](4) NULL,

       [succeeded] [bit] NOT NULL,

       [permission_bitmask] [bigint] NOT NULL,

       [is_column_permission] [bit] NOT NULL,

       [session_id] [smallint] NOT NULL,

       [server_principal_id] [int] NOT NULL,

       [database_principal_id] [int] NOT NULL,

       [target_server_principal_id] [int] NOT NULL,

       [target_database_principal_id] [int] NOT NULL,

       [object_id] [int] NOT NULL,

       [class_type] [varchar](2) NULL,

       [session_server_principal_name] [nvarchar](128) NULL,

       [server_principal_name] [nvarchar](128) NULL,

       [server_principal_sid] [varbinary](85) NULL,

       [database_principal_name] [nvarchar](128) NULL,

       [target_server_principal_name] [nvarchar](128) NULL,

       [target_server_principal_sid] [varbinary](85) NULL,

       [target_database_principal_name] [nvarchar](128) NULL,

       [server_instance_name] [nvarchar](128) NULL,

       [database_name] [nvarchar](128) NULL,

       [schema_name] [nvarchar](128) NULL,

       [object_name] [nvarchar](128) NULL,

       [statement] [nvarchar](2000) NULL,

       [additional_information] [nvarchar](2000) NULL,

       [file_name] [nvarchar](260) NOT NULL,

       [audit_file_offset] [bigint] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[DDL_Audit_Log_Temp](

       [event_time] [datetime2](7) NOT NULL,

       [sequence_number] [int] NOT NULL,

       [action_id] [varchar](4) NULL,

       [succeeded] [bit] NOT NULL,

       [permission_bitmask] [bigint] NOT NULL,

       [is_column_permission] [bit] NOT NULL,

       [session_id] [smallint] NOT NULL,

       [server_principal_id] [int] NOT NULL,

       [database_principal_id] [int] NOT NULL,

       [target_server_principal_id] [int] NOT NULL,

       [target_database_principal_id] [int] NOT NULL,

       [object_id] [int] NOT NULL,

       [class_type] [varchar](2) NULL,

       [session_server_principal_name] [nvarchar](128) NULL,

       [server_principal_name] [nvarchar](128) NULL,

       [server_principal_sid] [varbinary](85) NULL,

       [database_principal_name] [nvarchar](128) NULL,

       [target_server_principal_name] [nvarchar](128) NULL,

       [target_server_principal_sid] [varbinary](85) NULL,

       [target_database_principal_name] [nvarchar](128) NULL,

       [server_instance_name] [nvarchar](128) NULL,

       [database_name] [nvarchar](128) NULL,

       [schema_name] [nvarchar](128) NULL,

       [object_name] [nvarchar](128) NULL,

       [statement] [nvarchar](2000) NULL,

       [additional_information] [nvarchar](2000) NULL,

       [file_name] [nvarchar](260) NOT NULL,

       [audit_file_offset] [bigint] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[DDL_Audit_Failures](

       [event_time] [datetime2](7) NOT NULL,

       [sequence_number] [int] NOT NULL,

       [action_id] [varchar](4) NULL,

       [succeeded] [bit] NOT NULL,

       [permission_bitmask] [bigint] NOT NULL,

       [is_column_permission] [bit] NOT NULL,

       [session_id] [smallint] NOT NULL,

       [server_principal_id] [int] NOT NULL,

       [database_principal_id] [int] NOT NULL,

       [target_server_principal_id] [int] NOT NULL,

       [target_database_principal_id] [int] NOT NULL,

       [object_id] [int] NOT NULL,

       [class_type] [varchar](2) NULL,

       [session_server_principal_name] [nvarchar](128) NULL,

       [server_principal_name] [nvarchar](128) NULL,

       [server_principal_sid] [varbinary](85) NULL,

       [database_principal_name] [nvarchar](128) NULL,

       [target_server_principal_name] [nvarchar](128) NULL,

       [target_server_principal_sid] [varbinary](85) NULL,

       [target_database_principal_name] [nvarchar](128) NULL,

       [server_instance_name] [nvarchar](128) NULL,

       [database_name] [nvarchar](128) NULL,

       [schema_name] [nvarchar](128) NULL,

       [object_name] [nvarchar](128) NULL,

       [statement] [nvarchar](2000) NULL,

       [additional_information] [nvarchar](2000) NULL,

       [file_name] [nvarchar](260) NOT NULL,

       [audit_file_offset] [bigint] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[DDL_Audit_Check](

       [event_time] [datetime2](7) NOT NULL,

       [sequence_number] [int] NOT NULL,

       [action_id] [varchar](4) NULL,

       [succeeded] [bit] NOT NULL,

       [permission_bitmask] [bigint] NOT NULL,

       [is_column_permission] [bit] NOT NULL,

       [session_id] [smallint] NOT NULL,

       [server_principal_id] [int] NOT NULL,

       [database_principal_id] [int] NOT NULL,

       [target_server_principal_id] [int] NOT NULL,

       [target_database_principal_id] [int] NOT NULL,

       [object_id] [int] NOT NULL,

       [class_type] [varchar](2) NULL,

       [session_server_principal_name] [nvarchar](128) NULL,

       [server_principal_name] [nvarchar](128) NULL,

       [server_principal_sid] [varbinary](85) NULL,

       [database_principal_name] [nvarchar](128) NULL,

       [target_server_principal_name] [nvarchar](128) NULL,

       [target_server_principal_sid] [varbinary](85) NULL,

       [target_database_principal_name] [nvarchar](128) NULL,

       [server_instance_name] [nvarchar](128) NULL,

       [database_name] [nvarchar](128) NULL,

       [schema_name] [nvarchar](128) NULL,

       [object_name] [nvarchar](128) NULL,

       [statement] [nvarchar](2000) NULL,

       [additional_information] [nvarchar](2000) NULL,

       [file_name] [nvarchar](260) NOT NULL,

       [audit_file_offset] [bigint] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[DDL_Audit_Check_Offload](

       [event_time] [datetime2](7) NOT NULL,

       [sequence_number] [int] NOT NULL,

       [action_id] [varchar](4) NULL,

       [succeeded] [bit] NOT NULL,

       [permission_bitmask] [bigint] NOT NULL,

       [is_column_permission] [bit] NOT NULL,

       [session_id] [smallint] NOT NULL,

       [server_principal_id] [int] NOT NULL,

       [database_principal_id] [int] NOT NULL,

       [target_server_principal_id] [int] NOT NULL,

       [target_database_principal_id] [int] NOT NULL,

       [object_id] [int] NOT NULL,

       [class_type] [varchar](2) NULL,

       [session_server_principal_name] [nvarchar](128) NULL,

       [server_principal_name] [nvarchar](128) NULL,

       [server_principal_sid] [varbinary](85) NULL,

       [database_principal_name] [nvarchar](128) NULL,

       [target_server_principal_name] [nvarchar](128) NULL,

       [target_server_principal_sid] [varbinary](85) NULL,

       [target_database_principal_name] [nvarchar](128) NULL,

       [server_instance_name] [nvarchar](128) NULL,

       [database_name] [nvarchar](128) NULL,

       [schema_name] [nvarchar](128) NULL,

       [object_name] [nvarchar](128) NULL,

       [statement] [nvarchar](2000) NULL,

       [additional_information] [nvarchar](2000) NULL,

       [file_name] [nvarchar](260) NOT NULL,

       [audit_file_offset] [bigint] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

 

*/

 

 

GO

 

 

 

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[DatabaseDashboardNotification]

 

AS

 

SET NOCOUNT ON

 

DECLARE @HTML        varchar(MAX)

DECLARE @HTML_Header VARCHAR(max)

DECLARE @HTML_Body   VARCHAR(MAX)

DECLARE @HTML_Footer VARCHAR(max)

DECLARE @COUNT             INT

 

 

 

 

/***********************************

******* START DDL AUDITS

************************************/

DECLARE @DDLHTML           VARCHAR (MAX)

DECLARE @DDLHTML_Header    VARCHAR (MAX)

DECLARE @DDLHTML_Body      VARCHAR (MAX)

DECLARE @DDLHTML_Footer    VARCHAR (MAX)

DECLARE @DDLCOUNT          INT

 

DECLARE @AUDIT_COMPRESSED_INITIAL TABLE

              (TheID int identity(1,1)

              , event_time varchar(30)

              , LoginName varchar(256)

              , SQLInstance varchar(256)

              , DatabaseName varchar(256)

              , ObjectChanged varchar(256)

              , Succeeded varchar(3)

              )

 

INSERT INTO @AUDIT_COMPRESSED_INITIAL (event_time

       , LoginName

       , SQLInstance

       , DatabaseName

       , ObjectChanged

       , Succeeded)

select

       convert(varchar(35), event_time, 120)as event_time

       , server_principal_name as Loginame

       , server_instance_name as SQLInstance

       , database_name as DatabaseName

       , object_name as ObjectChanged

       , CASE WHEN succeeded = 0 THEN 'NO' ELSE 'YES' END as Succeeded

              From [DDL_Audit_Check]

where convert(varchar(35), event_time, 120) >= dateadd(hour, -36, getdate())

 

 

DECLARE @AUDIT_COMPRESSED_FINAL TABLE

              (TheID int identity(1,1)

              , event_time varchar(30)

              , LoginName varchar(256)

              , SQLInstance varchar(256)

              , DatabaseName varchar(256)

              , ObjectChanged varchar(256)

              , Succeeded varchar(3)

              )

INSERT INTO @AUDIT_COMPRESSED_FINAL (event_time

       , LoginName

       , SQLInstance

       , DatabaseName

       , ObjectChanged

       , Succeeded)

select event_time

       , LoginName

       , SQLInstance

       , DatabaseName

       , ObjectChanged

       , Succeeded

        from @AUDIT_COMPRESSED_INITIAL

group by

         event_time

       , LoginName

       , SQLInstance

       , DatabaseName

       , ObjectChanged

       , Succeeded

ORDER BY SQLInstance DESC, event_time ASC

 

 

SELECT @DDLCount = MAX(TheID) from @AUDIT_COMPRESSED_FINAL

 

IF @DDLcount<>0

       BEGIN

              SET @DDLHTML_Header ='

              <FONT STYLE="color: black;font-family: Verdana;font-weight: bold;font-size: 12;">DDL AUDIT PREVIOUS 36 HOURS </FONT>

              <TABLE>

              <table border="1" cellspacing="1" cellpadding="1">

              <TR colspan=2style="color: #A0522D; font-family: Verdana; font-size: 11; font-weight:bold" align=Left>

              <td>Server</TD>

              <TD>DB Name</TD>

              <TD>Login</TD>

              <TD>Object</TD>

              <TD>Success?</TD>

              </TR>

 

              '

 

              SET @DDLHTML_FOoter='</TABLE>

              <FONT STYLE="color: black; font-family: Verdana; font-size: 10;">For further details check the ADMINDB.DBO.[DDL_Audit_Check_Centralized] table on PSPSQL30\SSIS</FONT><BR><BR><BR>

              '

       END

Else

       BEGIN

              SET @DDLHTML_Header='<FONT STYLE="color: black;font-family: Verdana;font-weight: bold;font-size: 12;">DDL Audit Previous 36 hours </FONT>

       <br>   <FONT STYLE="color: black;font-family: Verdana;font-size: 10;">No DDL changes to report</FONT>'

              SET @DDLHTML_FOoter='</TABLE><BR><BR><BR>'

       END

 

 

select @DDLHTML_Body =''

 

--select *From  @DBA_Backup_Report_HTML

WHILE @DDLcount>0

 

       BEGIN

--print @count

              SELECT @DDLHTML_Body =@DDLhtml_body +'<TR style="font-size: 10">

                                         <TD>'+SQLInstance+'</TD>

                                         <TD>'+DatabaseName+'</TD>

                                         <TD>'+LoginName+'</TD>

                                         <TD>'+ObjectChanged+'</TD>

                                         <TD STYLE = "text-align: center;">'+Succeeded+'</TD>

</TR>'

              FROM  @AUDIT_COMPRESSED_FINAL where TheId=@DDLcount  

 

 

 

              SET @DDLCount =@DDLcount-1

 

             

       END   

 

 

 

/***********************************

******* END DDL AUDITS

************************************/

 

 

/******************************

***** BUILD EMAIL AND SEND

*****************************/

DECLARE @FinalFooter varchar(255)

SELECT @FinalFooter = '</body></html>'

 

SEt @HTML= @DDLHTML_Header+@DDLHTML_Body+@DDLHTML_FOoter--audits

+ @FinalFooter

 

--select  len(@html)

 

 

 

 

EXEC MSDB.DBO.SP_SEND_DBMAIL

@RECIPIENTS = '<list>'

, @SUBJECT = 'Audits'

, @BODY = @HTML

, @BODY_FORMAT = 'HTML'

, @IMPORTANCE = 'HIGH'

--print @html

 

 

 

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAudit_DDL_Changes]
FOR SERVER AUDIT [Server Audit DDL]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
AuthorNic | Comment1 Comment | Reference1 Reference | |
tagged , in , |

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: yahoo
    by Cory at Cory on October 18, 2010
    Google google

Reader Comments (1)

I am humbled. I'm definitely going to need to take a closer look at some of this. Thank you for sharing.

February 8, 2010 | Unregistered CommenterPeter

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Notify me of follow-up comments via email.