Audit log Design : Using AFTER INSERT, UPDATE, DELETE Trigger

In my last project, we had to implement Audit log functionality for SQL Server Data Changes. Due to very strict timeline(Project was of 2 months and Out of that Audit functionality was having only 10 days to implement), we were goggling for ready made design for Audit functionality. But our efforts gone into vain. Didn't find any good.

SQL Server 2008 is having in built functionality to capture data changes in the form of Change Data Capture Tool(CDC). We tried to convince the client to use the SQL Server 2008. But they didn't agree.:(. We had to go with SQL Server 2005.

We followed trigger based approach. We created AFTER INSERT, UPDATE, DELETE triggers on the all client tables, and recorded all the changes happened. The design was very simple. Hope this helps you guys.

Features included are:

1. Audit Log SHOULD have turn off & turn on switch.
2. Audit Log MUST capture information like who made the changes, what changes, when the changes occurred.
3. Audit Log SHOULD have rollback facility.
4. Retrieval of Audit details in Paging.
5. Audit retrieval WOULD have following APIs
   • Get all changes
   • Get all today’s changes.
   • Get all changes for period.
   • Get all changes for particular transaction.
   • Get single Audit Log entry for given Id

Block diagram to show entire architecture used for Audit:

Audit Log Table:

CREATE TABLE [Administration].[AuditLog]( [AuditLogID] [bigint] IDENTITY(1,1) NOT NULL, [TransactionID] [bigint] NOT NULL DEFAULT ([dbo].[ufn_GetTransactionID]()), [ApplicationName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetApplicationName]()), [ObjectID] [int] NOT NULL, [OperationID] [tinyint] NOT NULL, [PerformedBy] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetContextLogin]()), [PerformedAt] [datetime] NOT NULL DEFAULT (getdate()), [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PrimaryKeyColumn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PrimaryKeyValue] [int] NOT NULL, [PreviousValue] [xml] NULL, [NextValue] [xml] NULL

Table Schema:

	Name	Data Type	Description
	ID	bigint	Primary key - AuditLogID
	TransactionID	bigint	Transaction ID used while performing DML query. This column is used to find out all the changes made in single transaction.
	ApplicationName	nvarchar(max)	Application though which SQL query is made.
	ObjectID	int	Table Id – To know on which table the operation happened.
	OperationID	tinyint	1 = Delete 2 = Insert 3 = Update
	PerformedBy	nvarchar(100)	To store who has performed the operation.
	PerformedAt	datetime	Time when operation occurred.
	TSQL	nvarchar(max)	To store exact query which made this AuditLog entry.
	PrimaryKeyColumn	nvarchar(max)	To store primarykey column name of the Table on which operation occurred.
	PrimaryKeyValue	int	Primary key value.
	PreviousValue	XML	PreviousValue for changed row. Note: For insert operation this value is null.
	NextValue	XML	CurrentValue for changed row. Note: For delete operation this value is null.

Stored Procedures:

1. [Administration].[usp_EnableAuditLog]

Description: Enables Audit for database activities.

CREATE PROCEDURE [Administration].[usp_EnableAuditLog] AS BEGIN -- All 3 steps are done in transaction-block. -- 1. Create IUD trigger on all ARIC tables. -- 2. Create cleanup job. -- 3. Set Audit flag to true in database. END

2. [Administration].[usp_DisableAuditLog]

Description: Disables Audit.

CREATE PROCEDURE [Administration].[usp_DisableAuditLog] AS BEGIN -- Both below steps are done in transaction-block. -- 1. Delete all IUD triggers which were used for auditing purpose from all the ARIC tables. -- 2. Delete cleanup job. -- 3. Set Audit flag to false in database. END

3. [Administration].[usp_GetAuditLog]

Description: Retrieve log depending on specified input parameters.

CREATE PROCEDURE [Administration].[usp_GetAuditLog] @TransactionID BIGINT = NULL, @TableName NVARCHAR(100)= NULL, @UserName NVARCHAR(100)= NULL, @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @PageSize INT = NULL, @PageNumber INT = NULL, @TotalPages INT OUT AS BEGIN -- This Stored procedure is used by below 4 APIs -- a. Get all changes -- b. Get all today’s changes. -- c. Get all changes for period. -- d. Get all changes for particular transaction. -- Retrieves changes entries as per input criteria specified. -- Entries are returned in pages. END

4. [Administration].[usp_GetAuditLogEntry]

Description: Retrieves single log information according to AudiLogID.

CREATE PROCEDURE [Administration].[usp_GetAuditLogEntry] @AuditLogID BIGINT AS BEGIN -- Retrieves single log information according to AudiLogID END

5. [Administration].[usp_Rollback]

Description: Revert the update change occurred.

CREATE PROCEDURE [Administration].[usp_Rollback] @AuditLogID BIGINT AS BEGIN -- Reverts the update change occurred. END

 

Other Audit Log Fields can be found out by using In built SQL Server functions  or DMVs. For example,

TransactionID:

SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION;

ApplicationName:

SELECT program_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID

ObjectID:

Here ObjectID is the tableID in which trigger got fired. We calculated it as follows:

SELECT @ObjectID = parent_id FROM sys.triggers WHERE object_id = @@PROCID;

TSQL:

Here TSQL represents, the query responsible for firing the trigger.

DECLARE @ExecStr NVARCHAR(100);
DECLARE @inputbuffer TABLE  
(
          EventType NVARCHAR(MAX), 
          Parameters INT, 
          EventInfo NVARCHAR(MAX)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + LTRIM(RTRIM(STR(@@SPID))) + ')'

INSERT INTO @inputbuffer
EXEC (@ExecStr)
SET @TSQL = (SELECT EventInfo FROM @inputbuffer);

OperationID:

SELECT @DeletedCount = COUNT(1) FROM deleted
SELECT @InsertedCount = COUNT(1) FROM inserted
IF @InsertedCount= 0 AND @DeletedCount = 0 
       RETURN;
SET @OperationID = CASE WHEN @DeletedCount > @InsertedCount THEN 1 – DELETE
           WHEN @DeletedCount < @InsertedCount THEN 2 -- INSERT 
           WHEN @DeletedCount = @InsertedCount THEN 3 -- UPDATE
 END

etc…..

~ Kiran

Delete All Databases Except System Databases

Many times I require to create databases with different configurations for testing purpose.(This includes databases having special characters in their names, databases with different sizes, databases having data files on different locations. etc). As soon as I finish unit testing of the functionality , I have to delete those many databases manually from SQL Server Management Studio.

Today,Two times I had to delete 15 such databases manually :(. But now no more manual work. I have written SQL script for it :)

USE [master]
GO

DECLARE @database_name NVARCHAR (4000);

-- Collect all user created databases into cursor
DECLARE databases_cursor CURSOR FOR
SELECT name FROM sys.databases as d where d.database_id > 4
OPEN databases_cursor

FETCH NEXT FROM databases_cursor
INTO @database_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Dropping Database ' + @database_name
    DECLARE @cmd NVARCHAR(4000);
    SET @database_name = REPLACE(@database_name,']',']]');
    SET @cmd = 'ALTER DATABASE ['+@database_name+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ['+@database_name+'];'
    EXECUTE sp_executesql @cmd
    FETCH NEXT FROM databases_cursor
    INTO @database_name
END
CLOSE databases_cursor
DEALLOCATE databases_cursor

Disconnect all users from database

This is to reply Mohit's comment on my yesterday's post "Forcefully Disconnect All Users and Drop a Database" . I thought it would be nice to have it as new post rather than just a comment.

He had mentioned one scenario where there are times when you just have to disconnect other users from the database. We can acheive it by just killing all the processes connected to that particular database. I have written small stored procedure to do this:

CREATE PROCEDURE dbo.removeConnections
@database_name SYSNAME
AS
BEGIN
DECLARE @spid INT
DECLARE @query NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID(@database_name)
AND spid != @@SPID

OPEN processes

FETCH NEXT FROM processes
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'KILLing '+RTRIM(@spid)
SET @query = 'KILL '+RTRIM(@spid)
EXEC(@query)

FETCH NEXT FROM processes
INTO @spid

END
CLOSE processes
DEALLOCATE processes
END
GO

~ Kiran

Forcefully Disconnect All Users and Drop a Database

Yesterday, one of my colleague was telling me that he was not able to delete the database. When I checked the issue at his desk, I saw that other users were connected to that database without his knowledge and hence he was getting error while deleting it.

One way to get rid of it immediately:

ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE MyDatabase

~ Kiran

Confusing @@VERSION?

I was confused when i first ran the SELECT @@VERSION query and saw the result out of that query. Try to run the following query in SSMS:

PRINT @@VERSION
GO
PRINT 'Edition: ' + CONVERT(CHAR(30), SERVERPROPERTY('Edition'))
GO
PRINT 'Product Version: ' + CONVERT(CHAR(20), SERVERPROPERTY('ProductVersion'))
GO
PRINT 'Product Level: ' + CONVERT(CHAR(20),SERVERPROPERTY('ProductLevel'))
GO

Result:-
---------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Edition: Enterprise Edition
Product Version: 9.00.3042.00
Product Level: SP2

I have highlighted the confusing part in RED. This is Windows NT Service Pack Level, Not the SQL service Pack Level.

The reason i went to conclusion that the output provided by the query is very detailed one but i guess they missed to include sql server pack infrmation there.[;)]. Anyway we can get that information through SERVERPROPERTY('ProductLevel').

~ Kiran

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

More help on msdn.
~ Kiran

Leap Year has not been handled in SQL Server 2008 CTP

SQL Server 2008 CTP installables were not working on this year's FEB 29. I was using SQL Server 2008 CTP on one of my projects and found the above issue. All the installations worked properly on March 1.
~ Kiran

SQL Server Lesson 2 : sp_refreshview system stored procedure

When a new column is added to a table being referenced by a view, the new column will not automatically be reflected to the view, especially if the view is doing a SELECT * from the table.

For the new column to be reflected on the view, you have to refresh the definition of the view using the sp_refreshview system stored procedure. The sp_refreshview system stored procedure refreshes the metadata for the specified view.

Let us check this by example.

Create Table Customers:
CREATE TABLE [dbo].[Customers] (
[CustomerID] INT,
[CustomerName] VARCHAR(100),
[Address] VARCHAR(100)
)

INSERT INTO [dbo].[Customers] VALUES(100, 'Kiran Marke', 'Kalyan');
INSERT INTO [dbo].[Customers] VALUES(101, 'Ankit Jain', 'Ahmedabad');

CREATE VIEW [dbo].[CustomersView]
AS
SELECT * FROM [dbo].[Customers]

Now just do this
SELECT * FROM [dbo].[CustomersView]

This will give all records from the view with all the columns of [dbo]. [Customers] table.

Now let's add one more column to to above table
ALTER TABLE [dbo].[Customers] ADD [Gender] CHAR(1)
UPDATE [dbo].[Customers] SET [Gender] = 'M'

Now once again perform SELECT * FROM [dbo].[CustomersView]

This view will not include the new [Gender] column in the output.
EXECUTE sp_refreshview '[dbo].[CustomersView]'
After issuing this statement, the view will now include the new [Gender] column in the output.

SQL Server Shortcut keys.......

Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2
Bookmarks: Move to next bookmark. F2
Bookmarks: Move to previous bookmark. SHIFT+F2
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1
Editing: Clear the active Editor pane. CTRL+SHIFT+ DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT. CTRL+C
Editing: Cut. You can also use SHIFT+DEL. CTRL+X
Editing: Decrease indent. SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane. CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Increase indent. TAB
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT. CTRL+V
Editing: Remove comments. CTRL+SHIFT+R
Editing: Repeat last search or find next. F3
Editing: Replace. CTRL+H
Editing: Select all. CTRL+A
Editing: Undo. CTRL+Z
Execute a query. You can also use CTRL+E. F5
Help for SQL Query Analyzer. F1
Help for the selected Transact-SQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
Navigation: Window Selector. CTRL+W
New Query window. CTRL+N
Object Browser (show/hide). F8
Object Search. F4
Parse the query and check syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Move the splitter. CTRL+B
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Templates: Insert a template. CTRL+SHIFT+INSERT
Templates: Replace template parameters. CTRL+SHIFT+M
Tuning: Display estimated execution plan. CTRL+L
Tuning: Display execution plan (toggle ON/OFF). CTRL+K
Tuning: Index Tuning Wizard. CTRL+I
Tuning: Show client statistics CTRL+SHIFT+S
Tuning: Show server trace. CTRL+SHIFT+T
Use database. CTRL+U

SQL Server Lesson :1

If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

That is:

USE Kiran_DB
GO
CREATE PROCEDURE sp_who
AS
PRINT 'Some Text'
GO
EXECUTE sp_who


Will not print the above text.

This will still return information about current SQL Server users and processes.