How To Implement An Audit Log In Sql Server
Mar 3, 2011
We have an (asp.net) application that connects to a SQL Server 2008 R2 database using login credentials specifically setup for this application.
Our application access/modifies/deletes records via stored procedures, to which we pass the username of the user performing the action as a parameter.
We need to be able to keep an audit log of all updates and deletes on certain tables. The solution also needs to be as minimal as possible and not require any developer intervention.
The easiest way I can find, is to put a trigger on the table (A) that copies the 'old' data into a history table (A_History) The problem is that we need to be able to tell who performed the action, for an update this is easy we just look at username of the newly inserted record. But for a delete we do not have access to the username parameter sent to the stored procedure.
Is there any way to implement this without using things like CONTEXT_INFO(), because this would need to be added to every stored procedure and every call, which is bound to be forgotten.
View 1 Replies
Similar Messages:
Oct 11, 2010
I want to implement audit trail in my project. This implies keeping track of changes made in DB and later generating a report for specific data.
I'm looking for solutions other than triggers as in to have a generic solution for DB Audit trail in my project.
We'll probably be using Entity Framework 4.0. I want to explore the audit trail capabilities for the same any pointers in this directions would be helpful. also any ideas for DB Audit trail if we use Enterprise Library Data Application Block. Note:SQL Server 2008 has auditing features, but we'll not be using the Enterprise version.
View 1 Replies
Jul 13, 2010
We are developing a web application using asp.net and sql server. We are required to do an Audit trail for the application. As I understand this, an audit trail would basically be for all the Inserts, Updates and Deletes on the data base right? Now the way to approach this is that I have an Audit Trail Table in the DB that populates after every insert,update or delete is fired (Manually writing the script within the DAL). However any DB changes directly fired from SQL Management studio will NOT be recorded (for obvious reasons :P).
To cater for that I could create a trigger and that takes care of everything. I also did some googling and found out that SQL server has the ability to do audit trail. However the problem with using triggers is that I will NOT get the user information that logged in the website. I will get the sql user but I dont give two hoots about that, I am concerned about the website user. A solution that I figured out was either
a) Have an audit trail from my web application and also have trigger set up. On the audit report, I simply show an audit log from web application and and audit log from sql server.Obvious problems with this approach: over head. Writing to two different sets of tables on EVERY DB CHANGE.
b) I have a column called UserId ON EVERY DB TABLE. Then I create a trigger to capture all the DB changes. I pass this userId on every table I change (insert,update,delete) and the get this id from the trigger.
Obvious setbacks: unneccesary userid column in every table I do appologize for the long post. Basically I need an audit log that does log all the db changes (including direct hack to db) but at the same times gives me user login information for those db changes that were made FROM the web application.
View 4 Replies
Jul 29, 2010
I am using SQL server triggers to do the database auditing for my website. My problem is that I cannot pass the username who manipulate data into the Audit table. The username saved into Audit table is always the SQL server user name which is part of my connection string. Is there any better way to handle this?
View 6 Replies
Jul 1, 2010
I have an old 1.1 application. I want to add an audit log page which records every page user visits. who are they, which page they visit, and what time and so on. However, I do not want to touch the old code but just add another page to the website. Is it possible?
View 1 Replies
Oct 6, 2010
I'm currently using an SqlDataSource in ASP.NET/C# to let users insert, delete and update entries in a table/gridview. Every event needs to be written to an audit table.
I have easily implemented inserting and deleting - when inserting, the main info audited is just the parameter values of the insert query (e.Command.Parameters[0].Value.ToString() etc), and deleting is pretty much the same (just getting the ID in the delete query).
But with updating, I need to log which fields were changed and also their old values. How would I do this? As an example, here is the code for the inserting:
[Code]....
View 2 Replies
May 24, 2010
I have a question regarding the record audit trail with EF. Recently we have started working on a application that has to have an record audit trail.Just few tables have Guid as PK, the rest of them have Int32 standard Identity setup.
The quick solution we have found is to wire up an event handler on SavingChanges and get all Added, Deleted and Modified records. But the problem is that the Inserted records for the tables that have Int32 as PK are audited with PK value of 0.
the code for the event handler looks like this:
[Code]....
View 2 Replies
Mar 11, 2010
I have QuoteNumber(ddquotes.text),partnumber(txtpart),Lastmodifieddate(txtLMD) which is a time stamp on my webform.I am sending all these information to my vendaor as an email with attachment .
When i click on email button a message should be recorded in a Textbox saying so and so quotenumber,so and so partnumber has been sent on ,so and so datetime to so and so vendor.how can i acheive this with single button click.
View 3 Replies
Jun 9, 2010
I am in the process of creating an audit trail system. Simple enough. Certain fields require audit trail. What is the best design concept to allow this to work in multiple applications without having to change much? I would of course leave it to the admin of the site to add what fields should be audited, but the logic is the problem. Would implementing the interface IComparable be a place to start? My initial thinking is to compare 2 arrays against eachother and do an insert of what fields are in question. Array1 are the fields in the form, array2 are the fields from the sql table that require auditing.
View 8 Replies
Apr 19, 2010
Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving this? I know I could remove the trigger and have the delete stored procedure insert the audit entry prior to deleting but are there any other recommeded alternative?
If a username was passed as a parameter to the delete stored procedure, is there anyway to get this value in the trigger that's excuted when the record is deleted? I'm just throwing this out there...
View 4 Replies
Feb 1, 2010
My ASP.NET intranet web application uses Windows Authentication, and I would like to record the following details:
1) Windows ID
2) Session Start Time
3) Session Stop Time
4) URL being browsed to (optional)
I've got some basic code setup in "Session_Start" method of the Global.ASAX to log session start times (seen below), but that's it so far. I have the feeling this is a primitive approach and there are "better" ways of doing this. So I really have two questions:
1) Is this the right way to go about doing this? If not what are some other options?
2) If this is the right way, do I just need to drop some code in the "Session_End" method to record the time they exit, and thats a complete solution? Does this method always get called when they close the browser tab they have the site open in, or do they have to close the entire browser (I don't have logout functionality)? Any way users can skip over this session end method (or start for that case)?
Dim connsql As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionstring").ConnectionString)
Dim cmdsql As System.Data.SqlClient.SqlCommand = connsql.CreateCommand
cmdsql.CommandText = "BeginUserSession"
cmdsql.CommandType = Data.CommandType.StoredProcedure
Try
cmdsql.Parameters.Add("@windowsid", System.Data.SqlDbType.VarChar, 30, "windowsid")
cmdsql.Parameters("@windowsid").Value = Session("UserInfo").identity.name
If connsql.State <> System.Data.ConnectionState.Open Then connsql.Open()
cmdsql.ExecuteNonQuery()
connsql.Close()
Catch ex As Exception
Finally
If connsql.State <> Data.ConnectionState.Closed Then connsql.Close()
End Try
'Stored Proc records start time
View 3 Replies
Oct 29, 2010
i want to implement a simple client and server using asp.net.
Requirments: The client side will be a web application using asp.net where user can go on any common web browser (Firefox, IE...) and be able to use it without having to install any plug-ins or .NET. It will have a "Connect" button, once press it will connect to the server.
On the server side, which will just be a windows form or console application, once it detects a connection comin from the client, it accepts it, and send "Connected" back to the client side. The client side then respond back with "Hello world" to the server and display on the screen.
Should be able to work on LAN and over the internet WAN.
*Im trying to avoid tcp/sockets because it uses non-standard ports and usally it will be block by firewalls.
View 3 Replies
Dec 16, 2010
I want to implement functionality like if sql server is installed, do not install it.
View 1 Replies
Jul 19, 2010
I have a text box called "Address". I have a fairly large database with containing a table with columns: streetnumber, streetname, predirection, streettype, postdirection. I understand how to use the AJAX AutoCompleteExtender. County, City, State,Zip, Country do not apply to this search.
So I am looking for a good reference or some thoughts on working the back-end portion of this search. I guess some test cases might be a good start:
Valid Input:Orange 12 Orange 12 Orange Ave 12 N Orange Ave 12 N Orange Ave E 12 North Orange Ave East 12 NE Orange Ave 12 NE Orange Ave E South 12 South 12 South St 12 S South Street 12 South South Street South
My thoughts:It seems like the hardest part is going to be parsing the input text into corresponding streetname, pre/post directions, etc. I could just create a regular expression to parse this, but I am worried if that would be too restrive for this search IE:
Search: "12 South" --> "12 South Street" AND "12 South Orange Ave" AND "12 South South Street"
Could I could take advantage of full-text search and just send in the entire input string and see what comes out?
View 4 Replies
Jul 20, 2010
I have a graphical user interface for my company product.I want to secure the data being sent back and forth between client and server.
Is SSL one of the options? some1 tell me the steps on how to implement it in my application code.
Do i need to buy the certificate or can i make it.. which is the best choice?
I am logging in using FormsAuthenticationTicket as follows:
Session["userName"] = UserName.Text;
Session["password"] = Password.Text;
Session["domain"] = Domain.Text; [code]....
View 3 Replies
May 23, 2010
it's my first question to this nice site :) ... i use ckeditor.com in my asp.net web site and it have a great features but if any check the full feature example when inserting image you have two feature ( upload to server - browse server and choose image ) as shown in below image
http://i45.tinypic.com/2rmp5ds.jpg
My Questions : 1- Browse Server function integrate with another product called CKfinder .. i don't want to use it i search for such one but in asp.net and can be integrating with ckeditor ?
2- is there an example Upload image function that can save in SQL DB and can be integrated with ckeditor ?
View 1 Replies
Oct 31, 2010
I am new with SSIS, what I need is to search on a specific column in a table.
I am using asp.net with C#, and I dont have a cluw how to start and implement this.
View 1 Replies
Apr 27, 2010
I have Sql Server 2005 Express Edition ....
In which i want to implement Replication .....
can it be possible ..... ??
What are the Prerequisite to implement Replication for Sql Server 2005 Express Edition .....?
View 3 Replies
Feb 5, 2010
I am trying to implement a website that was developed on a test server and make it go live. I am running into an issue with a function within the DB that decrypts a password. Here is the function
USE [ward2004]
View 3 Replies
Oct 2, 2010
For the ASP.NET Menu Server Control whose RenderMode is set to "List", there is an "Orientation" property which decides whether the menu would render as a horizontal or a vertical menu. I have compared the two HTML source code and was unable to find out which part of the HTML/CSS code set the orientation of the menu(unordered list).
View 2 Replies
Jun 9, 2010
I have developed and multicolumn combo box server control using textbox and image(for arrow) as described in following sample
http://www.codeproject.com/KB/custom-controls/Multi_Column_Combo_Box.aspx
It is working fine. Now what i want is to implement is client side "onchange" event.
for e.g. if we want to call javascript function on selection change event of dropdownlist control then we can do it as follows
DropDownList1.Attributes("onchange") = "javascript:Validate();"
Here "onchange" is inbuilt client side event for dropdownlist control
Similarly i want such client side event that the is fired when selection is changed in my multicolum control,something like this
MultiColDropDown1.Attributes("onchange") = "javascript:Validate();"
View 1 Replies
Jan 4, 2011
To be able to implement paging i need to create a helper class (as mentioned on one example in the net ); so i have created a class that encapsulate the paging logic
public class PagingList<T> : List<T>
.
..
but when i reference this class in my view using the code below:-
Models.PagingList<Article>
The following error occurred. Server Error in '/' Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0246: The type or namespace name 'Article' could not be found (are you missing a using directive or an assembly reference?) Source Error:
[Code]....
Source File: c:UsersMohannadAppDataLocalTempTemporary ASP.NET Files
oot1f3f505f568b8e69App_Web_index.aspx.82d38c66.ffjpt45i.0.cs Line: 172
Show Detailed Compiler Output:
View 9 Replies
Jun 24, 2010
I want to implement the MVP in my project. I want that when I click a button appear some letters on the textbox.
View 5 Replies
Apr 1, 2011
I have a piece of code which loads either french or english text and 2 link buttons that allow to switch between.
[code]....
What would be an efficient way of displaying the link buttons and text if both English and French exist and the session exists in either EN or FR.
I've thought of using panels to hide the buttons or text, but the main problem is having the page refresh to set the Session variable. Response.redirect(URL) to refresh the page i suppose and i can't find a better way to display the text other than to Response.write it since then i'd need a ton of labels.
View 1 Replies
Jun 5, 2010
I've written a web form application which contains a multiview.. i have a drop down list which selects the view.. On selecting the option in the dropdown list , the content in the view changes... Well can any1 tell me how to implement multiview in asp.net mvc
View 3 Replies
