6

I'm converting an application from Access to SQL Server 2014. One of the capabilities of this tool is to allow users to create ad-hoc SQL queries to modify delete or add data to a number of tables.

Right now in Access there is no tracking of who does what so if something gets messed up on accident, there is no way to know who it was or when it happened (it has happened enough times that it is a serious issue and one of many reasons the tool is being rewritten).

The application I'm writing is a Windows application in C#. I'm looking for ANY and all suggestions on ways this can be done without putting a huge demand on the server (processing or space). Since the users are creating their own queries I can't just add a column for user name and date (also that would only track the most recent change).

We don't need to keep the old data or even identifying exactly what was changed. Just who changed data and when they did. I want to be able to look at something (view, table or even separate database) that shows me a list of users that made a change and when they did it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Spots Knight
  • 136
  • 4
  • 14
  • 1
    There is a built-in mechanism in SQL Server starting from 2008 version, Change Data Capture (CDC), which does just that. And it will also show you what exactly has been changed, too. – Roger Wolf Dec 03 '14 at 00:06
  • From everything I've read so far, CDC does not capture the username that did the change. Also I don't need to keep all the changes. that could cause the DB to grow a lot larger than I want. – Spots Knight Dec 03 '14 at 16:29
  • How are you handling access to the database in your c# app? Single userId or Windows authentication (meaning each user has a database account)? – UnhandledExcepSean Dec 03 '14 at 21:10
  • the Access databases don't have any security turned on so anyone can open them and the C# application doesn't have to worry about it. – Spots Knight Dec 04 '14 at 23:14

2 Answers2

0

You can consider to use triggers and a log table, this will work on all SQL Servers. Triggers are a bit more expensive that CDC, but if your users already are updating directly on your tables, this should not be a problem. I think this also will depend on how many tables you want to log.

I will provide you with a simple example for logging the users that has changed a table, or several tables (just add the trigger to the tables):

CREATE TABLE UserTableChangeLog
(
    ChangeID INT PRIMARY KEY IDENTITY(1,1)
    , TableName VARCHAR(128) NOT NULL
    , SystemUser VARCHAR(256) NOT NULL DEFAULT SYSTEM_USER
    , ChangeDate DATETIME NOT NULL DEFAULT GETDATE()
)

GO

CREATE TABLE TestTable
(
    ID INT IDENTITY(1,1)
    , Test VARCHAR(255)
)

GO
--This sql can be added for multiple tables, just change the trigger name, and the table name
CREATE TRIGGER TRG_TABLENAME_Log ON TestTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    --Can be used to get type of change, and wich data that was altered. 
    --SELECT * FROM INSERTED;
    --SELECT * FROM DELETED;

    DECLARE @tableName VARCHAR(255) = (SELECT OBJECT_NAME( parent_id ) FROM sys.triggers WHERE object_id =  @@PROCID);

    INSERT INTO UserTableChangeLog (TableName) VALUES (@tableName);

END

GO

This is how it will work:

INSERT INTO TestTable VALUES ('1001');
INSERT INTO TestTable VALUES ('2002');
INSERT INTO TestTable VALUES ('3003');
GO

UPDATE dbo.TestTable SET Test = '4004' WHERE ID = 2

GO

SELECT * FROM UserTableChangeLog

enter image description here

Jarle Bjørnbeth
  • 429
  • 3
  • 9
  • This maybe what I end up having to do, but I've heard Triggers can really affect performance. – Spots Knight Dec 03 '14 at 16:28
  • @Spots Knight: It depends on the trigger, if you run some "expensive" queries in your trigger, it will notably affect your performance when it triggers, since you have to wait for it to complete. In scenarios where you maybe have hundreds or thousands of different UPDATE/INSERT/DELETE queries in short intervals I would also reconsider to not use triggers. In this example however it will not use more performance than an extra "one row INSERT", witch would be hard to even measure. – Jarle Bjørnbeth Dec 03 '14 at 20:32
0

You haven't specified the SQL Server Version, anyway if you have a version >= 2008 R2 you can use Extended Events to monitor your system.

Extended Events Config

Extended Events Log

On stackoverflow you can read my answer to similar problem

Community
  • 1
  • 1
Max
  • 6,821
  • 3
  • 43
  • 59