default UpdatedBy Createdby columns in SQL Server 2008 tables

I recently came accross a conflict within my team where in a row was updated by "someone" and no one was ready to accept as to who did it. We always have columns开发者_开发百科 updated by, updated date, created by and created date columns and the person having read-write access to the DB can update data.

Now my question is that I would like to set these specific columns as read only and would like to default the values based on who is accessing the database, is this possible? If yes, any help will be deeply appreciated.

• NiK

In your current setup, if your developers have the kind of access to the data that it sounds like, there just isn't a bulletproof way to get the kind of integrity guarantee you're looking for. Anything you do for auditing, they can fake out and undo.

You might want to look into a Sql Server feature called "Change Data Capture". It requires at least enterprise edition, but it allows you to automate this and store your audit trail separate from the actual data (a good thing), in a way that can't be faked out by developers with high-privileged access.

You would have to restrict access to those tables through Stored Procedures and views.

With an Add/Update proc, you can ensure only your procedure updates the tracking columns. You would then remove write access to those tables. So that users MUST use the add/update procs to enter/modify data to those tables.

You can use triggers. I suppose, your colleges will not cheat and disable/enable the triggers.

CREATE TABLE [dbo].[myTable](
    [MyID] [uniqueidentifier] NOT NULL,
    [MyText] [nvarchar](50) NULL,
    [MyNumber] [int] NULL,
    [CreatedBy] [nvarchar](50) NULL,
    [UpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
    [MyID] ASC
)
GO

CREATE TRIGGER [dbo].[myTable_OnInsert]
   ON  [dbo].[myTable]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.myTable (MyID, MyText, MyNumber, CreatedBy, UpdatedBy)
        SELECT
            I.MyID, I.MyText, I.MyNumber, CURRENT_USER, CURRENT_USER
        FROM inserted AS I

END
GO

CREATE TRIGGER [dbo].[myTable_OnUpdate]
   ON [dbo].[myTable]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.myTable
        SET UpdatedBy = CURRENT_USER
    FROM inserted AS I

END
GO