SQL Server :: How To Pass Username Into Audit Table From .net Page
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?
I have an ASP.NET VB website in which I am using the Membership functionality to authenticate users. I am doing this through the Login control on the HTML page. I have working that it will authenticate the user and then opens a welcome screen in which I have a button they can push to open a Reports.aspx page. On that page I have a dropdown box that is being populated from a datasource connection query to my SQL Server database table. It is a hard coded query but want it to query the table off the username text value that the user provided on the login (i.e. select <something> from inventory where username = <username>). I have not behind code but only HTML at this point. Here it is ...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Login.aspx.vb" Inherits="Login" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
[Code] .....
Here is the Reports form that has the dropdown on it:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Report.aspx.vb" Inherits="Report" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
I am using an IFrame in one of my aspx pages. The source for the Iframe is an aspx page which consists of several links of other pages(All are developed in .net 2003) now i am using 2008.The Problem is for some of the pages there is query string which consists of username.I need to pass the username from the parent page to the IFrame pages.Is there any way to acheive this?
In my application i have login Page in that i have login Control of asp.net, when user clicks on submit button i am authenticating the user and and redirecting to default url.
now what i want is using address bar i want to authenticate the same process with out opening the login page and give user name and password in the login control. Directly i want to inject the username and password in the address bar and need the same functionality what the submit button does.
i have articles on net using post methods but i am not able to do this.
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.
I have a table setup in the database for users who are basically going to create a classified listing. How can I setup a diagram that would easily create a table relationship with the Users table, so that it automatically inserts the users username when they submit a new ad? If so, how? I've tried adding a UserName row to my table and creating a relationship with the Users table "UserName" row, but it wasn't accepting the relationship when I tried to save the diagram...apparently because the UserName row in the Users table isn't a primary key?
Also, one this is setup, what's the best way to provide the user a way to see a list of their ads and have the ability to edit them?
I am using ASP.Net 4.0/Visual Studio 2010. However, I think it would be the same if it was ASP.Net/VB.Net 3.5. I am inserting three form filed values into a database table. I have placed a LoginName Control on the page and the login name is properly displaying. I am using forms Authentication and it is set up properly in the web config file. I simply want to insert the logged in username value into the database table so I can track who created the records. I am happy to be able to insert the UnserName or the UserID value. I assume both of the values are derived out of the aspnet_Users table. Being able to insert either one would be fine but I believe UserID would be better because it will always be unique.
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.
is there anyway to pass in a Table Name to a DataSet?
I'm executing a stored procedure that is returning several recordsets. I have no problems w/ changing the DataTable's name from my Code Behind, but I was wondering if there was anyway the stored procedure could actually pass back the name of each recordset/datatable?
So it works fine i can hover on gridview rows. Now I need to add OnClick event. When Row is clicked I need to pass value from SQL server table to textbox. How can I Add something like onrowclickevent? where I can use clicked row arguments?
SELECT DISTINCT TblSections.SectionID, TblSections.Description FROM TblSections INNER JOIN UMG.Users ON TblSections.SectionID = UMG.Users.SectionID WHERE (TblSections.Description IS NOT NULL) AND (dbo.Users.LoginName = [@Session(UserName)]) ORDER BY TblSections.Description
I have problem in using Session name in the above BOLD WHERE clause.
I have created a page that pulls some data from a database based on a query string parameter.This works fine.
Then I added some site login, created roles and configured access to folders. I then restricted access to that page in a secure members folder using securityTrimmingEnabled="true" in the site map configuration.
I'm sure this is very simple but what I'd like to do now is use the login user name as my query parameter. I've been going round in circles trying to add it in the Page_Load event but not having any joy.
Cannot find a simple example that will call POST a service with username pass argument.
So, how to get the length of the service.
Code: Dim encoder As ASCIIEncoding = New ASCIIEncoding Dim data() As Byte = encoder.GetBytes(rawresp) Dim request1 As HttpWebRequest = CType(WebRequest.Create("http://www.sas.gr/MobileService/MobileService.asmx?wsdl&op=getCinemaMoviesProgram"), HttpWebRequest) request1.Method = "POST"
[Code] ...
Complains about wrong bytes. I can make it work with GET but i only get the schema.
Code: Dim request = DirectCast(WebRequest.Create("http://www.sas.gr/MobileService/MobileService.asmx?wsdl&op=getCinemaMoviesProgram"), HttpWebRequest) request.Method = "Get" ' request.ContentLength = 0 request.Credentials = New System.Net.NetworkCredential("WSl", "WSs") Dim response1 = DirectCast(request.GetResponse, HttpWebResponse) Dim reader = New StreamReader(response1.GetResponseStream()) Dim rawresp As String rawresp = reader.ReadToEnd()
This will only give the schema back, no data. Also if i reference the service there is no getCinemaMoviesProgram function to call, so I suppose it only works with post.
I have added a web reference using the wsdl file that was provided to me. I also created an instance from the proxy class as follows :
Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim DLObj As New WebReference.DrivingLicenseInfoServiceService
But after this am totally confused on the next steps and how to pass username and password and test the service connectivity.I have attached the autogenerated code.
I would like to take your help for a small task of mine. I have dataset whose contents have been converted as xml, the contents of which needs to be sent to a stored procedure. How do i go about creating methods in the data layer and the stored procedure.What should be parameter type in the data layer's method and what should be the parameter type in the stored proc. I dont want to use a varchar at the stored proc level because it is limited to a length of only 8000 characters.
I tried both, loading the usercontrol when needed and I also tried adding it directly into the aspx.
Nothing worked. With the second one (what's meant to be the easiest option) I tried with delegates and events (but can't access de usercontrol event from page) and tried with properties (I can access properties but they're always empty)
I have a CreateUserWizard control to be able to allow an anonymous user to create an account. Provided that all entered information is correct that the user enters, a new account is created for him/her. But also, when this user is created, I want to [b]also[/b] add the username to another table in my ASPNETDB database. How exactly can I "tap" into the event that creates a user and adds it to the appropriate tables (aspnet_Users, Members, etc.) and make it add the username to yet another table?
I have set up security on my ASP.NET site and currently have 1 user (soon to be more!). I would like to send the logged-on user's username to a variable, to input this value to a custom-designed stored procedure on my SQL database (for recording search statistics).Does anyone know how I can harness the current logged-on users username? I am using VB. Will the following achieve the result?session(username)If so, how do I implement this? I tried using "Current_User" in the SQL stored procedure, but obviously this did not record the ASP.NET user. Also, if the current user has not logged onto the site, I would like the "username" value to be "annonymous".
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?
I want to compare username and password store in database table to the textbox values for that I am using session variable. How to use dataset for session variable?
How to retrive values from database table using dataset?
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:
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.