SQL audit log

Does SQL server have a way to create a audit log of all Add, Update, and Deletes?  And, I want to track these by user - and by user, I mean a user table that I have defined.
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Starting in SQL 2008, MS added SQL Server Audit which does what you're asking, though you would have to figure out how to add in your user information. I believe that requires Enterprise Edition, though. Another solution is to only allow database changes via stored procedures, and then you can add changes to your own change log table in the proc code. I've also created triggers on the tables to add audit info to a custom audit table. Here the ObjectID comes from my own Object table, and all changed values are converted into a VARCHAR so they can all be stored in the same table:
CREATE TABLE [dbo].[core_Audit](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ObjectID] [int] NOT NULL,
	[TableName] [nvarchar](50) NOT NULL,
	[FieldName] [nvarchar](50) NOT NULL,
	[SourceID] [uniqueidentifier] NOT NULL,
	[DataType] [varchar](50) NOT NULL,
	[OldValue] [nvarchar](255) NOT NULL,
	[NewValue] [nvarchar](255) NOT NULL,
	[UserModified] [int] NOT NULL,
	[DateModified] [datetime] NOT NULL CONSTRAINT [DF_core_Audit_DateModified]  DEFAULT (getdate()),
 CONSTRAINT [PK_core_Audit] PRIMARY KEY CLUSTERED 

Open in new window

And here is part of the trigger - this code block is required for each field you want to track:
-- [OrderNumber]
INSERT INTO dbo.core_Audit ( ObjectID , TableName , FieldName , SourceID ,DataType ,OldValue ,NewValue ,UserModified ,DateModified)
	SELECT 22, 'core_Order', 'OrderNumber', t1.GID, 'varchar (50)', LEFT(ISNULL(CAST(t2.OrderNumber AS NVARCHAR(4000)), 'NULL'), 255), LEFT(ISNULL(CAST(t1.OrderNumber AS NVARCHAR(4000)), 'NULL'), 255), t1.UserModified, @Date
	FROM INSERTED t1 INNER JOIN DELETED t2 ON t1.ID = t2.ID
	WHERE ISNULL(t1.OrderNumber, '')  <> ISNULL(t2.OrderNumber, '')

Open in new window

0
HLRosenbergerAuthor Commented:
Russell - thanks.   I also found this post which is similar to your post.

http://www.softcodearticle.com/2012/11/imlementing-generic-audit-trail-trigger-in-sql-server/

I got this to work and it records Inserts and Deletes, but not Updates.  Any thoughts why?
0
HLRosenbergerAuthor Commented:
ah, I found a better solution that works without error.

http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randy Knight, MCMPrincipal ConsultantCommented:
Change Tracking or Change Data Capture (depending on if you want to know the actual data) would be another option.
0
HLRosenbergerAuthor Commented:
This solutions works great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.