CODEWITHSUNDEEP
asp.netsql-server
dvjanm
dvjanm

Reputation: 2381

How to log inserts, updates in SQL Server?

I am wondering what would be the best practice to logging when a user inserts or updates some data from my ASP.NET application.

In my application I need to know which user made the change, I think I have to options:

  • Register my ASP.NET users into the SQL Server adding them the rights for the tables needed, and store for each of my users individual connectionstring. Thus I could use the currently logged in user's connectionstring when connecting to the database, so that I could write T-SQL triggers for SELECT and UPDATE.

  • The other option I thought of is that I can write stored procedures that get the ASP.NET user name as input parameter and makes the log for me, (I use only one connectionstring in the application). After modifying the database from the application I would call this procedure giving the currently logged in user's username.

The second option seems simpler to me, but I think that for that purpose I should choose the first option.

Upvotes: 1

Views: 3187

Answers (1)

Richard Hooper
Richard Hooper

Reputation: 819

Go for the second option. Managing users in a database creates more of a headache for you and what happens if you have to move the database server? You would need to update all of the connection strings for all the users. It just creates an extra admin overhead.

Furthermore, if you need to log all the changes in the database then I have come up with something that I have used in the past to store all changes made to a database over time. Have a look, it might be useful.

http://richhooper.wordpress.com/2012/06/11/sql-server-row-level-versioning/

Upvotes: 1

Related Questions