SQL SERVER 2008 Express Edition - Data Auditing Best Practice

Question

SQL SERVER 2008 Express Edition - Data Auditing Best Practice

SHearn

Old Hand

Points: 324
More actions
September 16, 2010 at 10:22 am

#238399

I would like to provide a mechanism to write data changes to a log file residing on the same machine as the sql server instance. If a user added, updated or deleted a record in a table I would like to track that change. I keep users in a users table in the database. Users log into the software as one of those user entries found in the table. What is the best way to go about this using SQL Server 2008 Express Edition?
For example:
If user XXX with user id 2 updates a record in the Customers table, I would like to store in a file the table name, the data change(s), the user id and the datetime the change(s) occurred.
Thanks for any advice, it is much appreciated.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323754 More actions · Answer 1

ok correct me if i am wrong, but you have a table with ApplicationUsers, so users 'Bob' and 'Tom' are in a table, but the application connects witha single SQL Server logon/username, ie 'sqluser'?

if that is true, there's no way at the SQL server side to know that the Appuser 'Bob', who is using the database user 'sqluser';

your application is most likely using some sort of an Update command to sychronize changes...the place to save "who did what" is in that function within the Application itself; if it's .NET, each row that has changes can be detected, and you could do something with a log or a table based on that, and the user context of who is logged intoyour app.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Sharon Hearn-319037 (9/16/2010)
I would like to provide a mechanism to write data changes to a log file residing on the same machine as the sql server instance. If a user added, updated or deleted a record in a table I would like to track that change. I keep users in a users table in the database. Users log into the software as one of those user entries found in the table. What is the best way to go about this using SQL Server 2008 Express Edition?
For example:
If user XXX with user id 2 updates a record in the Customers table, I would like to store in a file the table name, the data change(s), the user id and the datetime the change(s) occurred.
Thanks for any advice, it is much appreciated.

You may want to check if SQL Server Auditing - which is supported by Express Edition as far as I remember, fits your needs. Here... http://msdn.microsoft.com/en-us/library/cc280386.aspx

If not, triggers on the to-be-audited tables may be a good starting point.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SHearn Old Hand Points: 324 More actions · Answer 3

Thanks for the response. I don't think the audit capability is in the express edition. I went into the SQL Server Management Studio and do not see Audits under the security node. I also tried to issue the following command in the query window:

CREATE SERVER AUDIT [TestAudit] TO FILE (FILEPATH = 'C:\TestAudit');

I received an error stating that Auditing is not available in this edition of SQL Server.

SHearn Old Hand Points: 324 More actions · Answer 4

Lowell - You are correct. I do have ApplicationUsers, 'Bob' and 'Tom', in a table. The application connects with a single SQL Server logon/username. I thank you for your suggestion of using the frontend to facilitate audit tracking.

jerry 41468 Valued Member Points: 67 More actions · Answer 5

In my app, we also have multiple application user logins but use a single SQL login. Each table has a column that gets updated with the logged in user ID as sent in the data packet.

We use triggers to record that information along with the data changes and it works pretty well.

SHearn Old Hand Points: 324 More actions · Answer 6

Thank you Jerry 41468! I appreciate your response. This is my first post and I have had such helpful advice. I sincerely appreciate it. 🙂