Hello all :)

I have created an intranet site for the company i work for, it is an online phone book for people in the company. I have done a search, modify and insert pages into a SQL Database.

What they want me to do before they make it live across the company is to record any changes that are made to the database, i.e. whos record was changed, what changes where made, when and by whom. I am using Windows Authentication in IIS, but i have just locked down the modify & insert pages via NTFS permissions, because i couldnt figure how to get an auto login via the Domain Username.

Any help would be much appreciated :D

I am not sure I understand your problem. Can you be more specific as to what information you need / what question you're asking??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Forever Changed by Lou Reed & John Cale (Track 14 from the album: Songs For Drella) What's This?

I want to record who makes changes to the database, and what changes they have made & log this into the SQL database.

It depends on your setup.

If you're using either basic or integrated security to connect to your database, you can use triggers on the tables that you want to monitor. Then you can use suser_sname (I believe) to get at the user's account name and insert that name in your audit table.

Alternatively, you can expand your queries (or fire two of them) to update your audit table with our own custom data, possible including the user's name used in the application.
Search Google for audit trigger sql server and I'm sure you'll find lots of useful stuff.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Two Clouds Above Nine by Deee-Lite (Track 6 from the album: Infinity Within) What's This?

The only problem i have is i only have one dbo username connecting to the SQL Server, therefore every change made will log it as SQLUser as appossed to the NT Domain user.

Here is the though i have had, but i dont know if it would work or how to go about doing it:-

When pressing the update or insert button, it will update the main database and also insert the new details into a seperate database & logging there windows nt account name, hence creating an audit trail at the same time...

Do you think this is possible ?

Yeah, you can. Like I said in my previous post: So for each query you fire, update the audit table as well.

However, this means quite a lot of work. For each query in your database you need to write additional Sql statements and data access code to perform the update. You also need to maintain your users in that database, and maintain separate user accounts. You minimize the amount of work by writing a generic function that does this for you. You can include this function in all the pages that need auditing.

It may be easier to update your existing code and pass in the user's name to your database (e.g. stored procedures for example). Then you can use this name to update your audit table, possible using triggers.


Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.