SQL Saturday #62 Code Samples

I am very sorry that I am just now posting the code samples I used at last weeks SQL Saturday in Tampa.

I am finally getting caught up and will be home for an entire week so there are no excuses for posting!!

Thanks for all who attended and please feel free to contact me with any questions.

--************************************************************
--Create server side trace using t-sql demo
--************************************************************

-- Create a Queue
DECLARE @rc                INT
DECLARE @TraceID        INT
DECLARE @maxfilesize    BIGINT
SET        @maxfilesize = 50


EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, N'C:\Audit', @maxfilesize, NULL, 100 
IF (@rc != 0) GOTO error

-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 14, 12, @on
EXEC sp_trace_setevent @TraceID, 15, 15, @on
EXEC sp_trace_setevent @TraceID, 15, 16, @on
EXEC sp_trace_setevent @TraceID, 15, 9, @on
EXEC sp_trace_setevent @TraceID, 15, 13, @on
EXEC sp_trace_setevent @TraceID, 15, 17, @on
EXEC sp_trace_setevent @TraceID, 15, 6, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on
EXEC sp_trace_setevent @TraceID, 17, 12, @on
EXEC sp_trace_setevent @TraceID, 17, 1, @on
EXEC sp_trace_setevent @TraceID, 17, 9, @on
EXEC sp_trace_setevent @TraceID, 17, 6, @on
EXEC sp_trace_setevent @TraceID, 17, 10, @on
EXEC sp_trace_setevent @TraceID, 17, 14, @on
EXEC sp_trace_setevent @TraceID, 17, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 9, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 2, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 6, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 15, @on
EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 9, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 6, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 13, 12, @on
EXEC sp_trace_setevent @TraceID, 13, 1, @on
EXEC sp_trace_setevent @TraceID, 13, 9, @on
EXEC sp_trace_setevent @TraceID, 13, 6, @on
EXEC sp_trace_setevent @TraceID, 13, 10, @on
EXEC sp_trace_setevent @TraceID, 13, 14, @on
EXEC sp_trace_setevent @TraceID, 13, 11, @on


-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
DECLARE @databaseid    INT

--Set the databaseid to filter only on the fuel database
SELECT @databaseid =  DB_ID(N'AdventureWorks2008R2')

EXEC sp_trace_SETfilter @TraceID, 3, 0, 0, @databaseid
-- Set the trace status to start
EXEC sp_trace_SETstatus @TraceID, 1

-- Display trace id for future references
SELECT TraceID=@TraceID
GOTO finish

ERROR: 
SELECT ErrorCode=@rc

FINISH: 
GO


/*
-- This commented code can be used to stop and delete the trace
-- Stop and remove the trace when done
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus @traceid = 2 , @status = 2
*/ 


--************************************************************
--Create DDL audit
--************************************************************

--Create table to hold DDL events
USE AdventureWorks
GO
CREATE TABLE AuditLog
(ID           INT PRIMARY KEY IDENTITY(1,1),
Command    NVARCHAR(1000),
PostTime   NVARCHAR(24),
HostName   NVARCHAR(100),
LoginName  NVARCHAR(100)
)
GO

-- create DDL trigger
CREATE TRIGGER Audit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)

SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
    'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd, '
', '')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]',
    'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
    'NVARCHAR(100)')
SET @hostname = HOST_NAME()

INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)
SELECT @data

GO

-- perform DDL operations
UPDATE STATISTICS Production.Product
GO
CREATE TABLE dbo.Test(col INT)
GO
DROP TABLE dbo.Test
GO

-- View log table
SELECT *
FROM dbo.AuditLog
GO

DELETE [AuditLog]
WHERE ID = 4
-- clean up
DROP TRIGGER Audit
ON DATABASE

DROP TABLE dbo.AuditLog
GO

--************************************************************
--SQL Audit Demo
--************************************************************

USE master;
GO 

--Create a server audit for the application.  Since the QUEUE_DELAY is not specified
--The default is 1000ms or one minute and if failure occurs the SQL serveice will 
--continue to run as the default parameter for ON_FAILURE is continue
CREATE SERVER AUDIT Log_Audit
TO APPLICATION_LOG;
GO

--Create a server audit for a file.  QUEUE_DELAY 0 so the audit is synchronous
--ON_FAILURE is set to fail so if a failure occurs the sql service will shutdown
CREATE SERVER AUDIT File_Audit
TO FILE(FILEPATH='C:\AUDIT\')
WITH(ON_FAILURE=SHUTDOWN,
     QUEUE_DELAY = 0);
GO

--Create a server audit for the application that will be used to record audit level events.  
--Since the QUEUE_DELAY is not specified the default is 1000ms or one minute 
--and if failure occurs the SQL service will 
--continue to run as the default parameter for ON_FAILURE is continue
--CREATE SERVER AUDIT Audit_Audit
--TO APPLICATION_LOG;
--GO

--The audits are available in the sys.server_audits catalog view
SELECT * 
FROM sys.server_audits;
GO

--The aduits are not enabled/on so not visible in the extended events DMV sys.dm_xe_session_targets view
SELECT *
FROM sys.dm_xe_session_targets

SELECT *
FROM sys.dm_xe_packages

--Enable the server audits
ALTER SERVER AUDIT Log_Audit
WITH (STATE=ON)
GO

ALTER SERVER AUDIT File_Audit
WITH (STATE=ON)
GO

--ALTER SERVER AUDIT Audit_Audit
--WITH (STATE=ON)
--GO

--************************************************************
--View the application log and two entries show the audit states
--were enabled since this is intrinsically audited
--************************************************************

--The audits are now visible in the    extended events DMV    
SELECT *
FROM sys.dm_xe_session_targets dt

--Create a database audit specification for any SELECT or INSERT 
--statement on the Person.Person by a member of dbo and any SELECT 
--statement on HumanResources.Employee by anyone 
USE AdventureWorks2008;
GO 

CREATE DATABASE AUDIT SPECIFICATION Person_Employee 
FOR SERVER AUDIT File_Audit  
ADD(SELECT, INSERT ON Person.Person BY dbo),
ADD(SELECT ON HumanResources.Employee BY public)
  WITH (STATE = ON)
GO

--Run SELECT queries against the objects that are being audited
SELECT *
FROM Person.Person;
GO

SELECT *
FROM Person.Person
WHERE LastName = 'Duffy';
GO

SELECT JobTitle,
       'David Rocks!!'
FROM HumanResources.Employee;
GO

--Using the sys.fn_get_audit_file function review the audit file
SELECT event_time,
      action_id,
      session_server_principal_name,
      statement
FROM sys.fn_get_audit_file('C:\Audit\*', DEFAULT, DEFAULT);
GO

SELECT *
FROM sys.fn_get_audit_file('C:\Audit\*', DEFAULT, DEFAULT);
GO

--Since the server audit is being used by another audit specification 
--this will fail
CREATE DATABASE AUDIT SPECIFICATION Wont_Work_Dude 
FOR SERVER AUDIT File_Audit  
ADD(SELECT, INSERT ON Person.Person BY dbo),
ADD(SELECT ON HumanResources.Employee BY public)
  WITH (STATE = ON)
GO

--******************************************************************************************
--Stop the SQL service and delete the C:\Audit folder and attempt restart.  The service
--Will fail to restart since the log can not be written to
--******************************************************************************************

--CREATE SERVER AUDIT SPECIFICATION Audit_Specification
--FOR SERVER AUDIT Audit_Audit
--    ADD (ALTER_AUDIT_GROUP);
--GO

--******************************************************************************************
--Stop the Database audit specification and then the server audit
--Veiw the c:\Audit folder and note that the audit files still exist
--******************************************************************************************

USE AdventureWorks2008;
GO

ALTER DATABASE AUDIT SPECIFICATION Person_Employee
WITH (STATE=OFF);
GO

DROP DATABASE AUDIT SPECIFICATION Person_Employee;
GO

USE master;
GO

ALTER SERVER AUDIT Log_Audit
WITH (STATE=OFF)
GO

ALTER SERVER AUDIT File_Audit
WITH (STATE=OFF)
GO

DROP SERVER AUDIT Log_Audit;
GO

DROP SERVER AUDIT File_Audit;
GO

--Alter the AuditLogging specification turning the state off and drop it 
USE [master]

GO

ALTER SERVER AUDIT SPECIFICATION AuditLogging
WITH (STATE=OFF);
GO

DROP SERVER AUDIT SPECIFICATION AuditLogging;
GO

ALTER SERVER AUDIT Audit_Audit
WITH (STATE=OFF)
GO

DROP SERVER AUDIT Audit_Audit;
GO