Auditing Select Queries , so that the full query is logged

Hi,

I currently looking into auditing, we want to see who (and when) has 
viewed/updated patient information on our database. (I am using SQL Server 
2008 and our application is developed in VS 2008).

I have set up an audit , and a database audit to log any selects for our 
patient table.
However instead of showing the full select statement i.e.

select top 1 * from patient where patientnumber = 111111

it is showing

select top 1 * from patient where patientnumber = @1

So i also enabled change tracking on the database, this was great if i 
entered the query in management studio as it logged the full query, however 
when performing this in the application it still returns the incomplete 
select statement. (I have read somewhere that this may be because i am using 
ADODB to connect to SQL).

1) Is it possible to obtain the complete SQL query with just auditing turned 
on (i.e. not using change tracking)

2) If not, is there any known reason why i cannot get the complete sql 
statement with change tracking on from within my application? (please let me 
know if more details are required).

3) If there any way of auditing the data returned by an select query?

Thanks

Dan Sayer

0
Utf
11/12/2009 4:57:01 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
1053 Views

Similar Articles

[PageSpeed] 58

1) I am not sure about

2) didn't think change tracking would help out at all for SELECT activity, 
just DML activity.  And it doesn't have nearly enough information anyway for 
auditing purposes

3) You CAN audit the data returned by sql server, but it takes network 
packet capture to do so.  You can use network packet capture (and 
disassembly) to audit SELECTs sent to the database as well.  IIRC a company 
called WhiteSands had such a product out several years ago.


-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Dan Sayer" <Dan Sayer@discussions.microsoft.com> wrote in message 
news:A040FCFA-5A97-46F6-89CE-4081CC572F7E@microsoft.com...
> Hi,
>
> I currently looking into auditing, we want to see who (and when) has
> viewed/updated patient information on our database. (I am using SQL Server
> 2008 and our application is developed in VS 2008).
>
> I have set up an audit , and a database audit to log any selects for our
> patient table.
> However instead of showing the full select statement i.e.
>
> select top 1 * from patient where patientnumber = 111111
>
> it is showing
>
> select top 1 * from patient where patientnumber = @1
>
> So i also enabled change tracking on the database, this was great if i
> entered the query in management studio as it logged the full query, 
> however
> when performing this in the application it still returns the incomplete
> select statement. (I have read somewhere that this may be because i am 
> using
> ADODB to connect to SQL).
>
> 1) Is it possible to obtain the complete SQL query with just auditing 
> turned
> on (i.e. not using change tracking)
>
> 2) If not, is there any known reason why i cannot get the complete sql
> statement with change tracking on from within my application? (please let 
> me
> know if more details are required).
>
> 3) If there any way of auditing the data returned by an select query?
>
> Thanks
>
> Dan Sayer
> 


0
TheSQLGuru
11/12/2009 9:03:53 PM
Reply:

Similar Artilces:

full path of excel file
how to show the full path of excel file in the foot of page while printing? excel 2K only gives me the option for the name of the file not the full path. Private Sub Workbook_BeforePrint(Cancel As Boolean) With activepage.PageSetup .LeftFooter = ThisWorkbook.FullName End With End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing dire...

Merge different queries
Hello all, I have two tables where I enter my stock trades as well as foreign exchange (FX) trades. The two tables contain different fields as the trading instruments require different entries. I then created two seperated queries in order to calculate the profit from each stock and each FX trade. I need two queries as the calculations are different, too. Now, I would like to merge the two queries in order to see my overall profit and losses no matter if the trade is a stock or FX trade. That is, in the final query I would like to see the trade ID (which is the primary key in eac...

how to log in to office online
how do access my microsoft office basics. have tried through msn and cannot get on. thank you If you are refering to Office Live Basics please go here www.officelive.com -- Chester C. Coronel Junior BSIT Student University of Asia and the Pacific, Philippines Read the StudentEmpowered Blog!http://msforums.ph/blogs/chestercoronel "romanticingpoet" wrote: > how do access my microsoft office basics. > > have tried through msn and cannot get on. > > thank you ...

Checkbox to a query
I have a form with a checkbox; I'm trying to build a query that grabs that value from the checkbox (-1), and use it as a criteria. In the criteria, I want to say: if the checkbox is checked, give me all records that do not have a -1, otherwise give me all records. So to clarify: I have two tables. first one is "new table" and the second one is "archive table". I want to build a query, so that if the checkbox is checked, all records in the "new table" that are NOT in the "archive table" will be shown. If the checkbox is not checked, then I want all...

Selecting Multiple Cells.
I know how to select a range of cells by specifying reference in nam box. Examples: A10:A20 (The range of cells in column A and rows 10 through 20) 5:5 (All cells in row 5) However, I am attempting to select every third cell going down colum A, beginning with A3. I can enter A3, A6, A9 (This will select just those cells.) Is there a shortcut I can use to continue this pattern of every thir cell indefinitely? (I actually need to select every third cell down t A4950.) Thank you for any assistance -- Message posted from http://www.ExcelForum.com Here's one way: 1. Put this in ...

RMS should have auto log off if system is idle
it would be nice to have auto log off if the system is idle for a specific amout of time ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=555af75d-267a-4ecf-8296-9da84065f19...

How do I select every nth row?
Hi I have a long list of data. Every fourth row contains the sort of data (ID, name, points...) Unfortunately the data is all in one long column. Can someone tell me how to select out every fourth row? Maybe something that can give me the row number modulo 4? My overall goal is to move this data into an Access table, but I'm having trouble because of the format. Thanks. Ray. If your data starts in A2 and you want A2, A6, A10 and so on, use a help column adjacent and next to the first cell with data use =MOD(ROW(1:1),4)=1 copy down if you want A5, A9, A13 use =MOD(ROW(1:1),4)=0 ...

Check Worksheet Name and Select if Meets Criteria
Hi everyone, i have what i think is a very easy question. I'm trying to select all the worksheets in my workbook that start with the "Data". i wrote this vba code, and even though i put a watch on the left formula and it shows "Data" in the watch window, it never selects the sheet. Any help would be much appreciated!!!! Sub SelectCockpit() ' ' Selects all tabs with cockpit in first 6 digits Dim wksSheet As Worksheet Sheets(1).Activate For Each wksSheet In Worksheets If Left(wksSheet.Name, 4) = "Data" Then wksSheet.Select End If Next ...

query problem 12-01-09
I've got a table of Organizations and a table of Memberships. Memberships have an OrgID and one Org can be part of many Memberships. Memberships have an expiration date. I'm trying to build a list of Organizations that have at least one non- expired Membership associated with it. I'd like each current Org to show once in a list (for an unbound combo box). I tried: SELECT tblOrganizations.OrganizationID, tblOrganizations.Name, [tblMemberships.Anniversary]>(Date()-60) AS [Current] FROM tblOrganizations INNER JOIN tblMemberships ON tblOrganizations.OrganizationID ...

can open 1 picture unless logged in as Administrator???
Help please????!!! Unless i log in as Administrator, i can only insert 1 picture in MS Publisher 97? I get told the graphics filters are not installed, but i have done a full install? Ms clients are running windows 2000 OS and are locked down via group policy. Any help would be great Thanks, Carl. Publisher (especially the older versions I hear) doesn't like playing with networks. -- JoAnn Paules MVP Microsoft [Publisher] "Carlos1981" <Carlos1981@discussions.microsoft.com> wrote in message news:642AAFF8-D25F-4AC5-B90E-33AC25E17663@microsoft.com... > Help p...

referential integrity selected but cascade delete is greyed out
I've inherited an Access application. The delete process used to work. Access 2007 was installed last month and now the deletion does not work. The application is still running as an mdb. The relationships are a many to one. The delete is attemped on the many sided table. There are two one sided tables. The resulting error message is "The record cannot be deleted or changed because table 'Master Address ContactMethods' includes related records." When I checked, referential integrity was selected and the cascade options were greyed out. I cannot make any ...

Popup a message when query finished
I have a form that has a command button with code that updates 7 tables by a series of 7 queries. Is there any way to popup a message box to indicate to the user that all the queries have run successfully and they can move on? Although it happens very quickly there is no visual evidence to the user that the query is running or finished. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1 Yes, you can put a Msgbox...

May I post here questions about SQL queries against Excel via ODBC?
and if not, can you recommend of a relevant forum? For example, I'm desperately looking for an answer to the question: How can I retrieve the row number of a record in a query? Somethin like: select x.*,rownum() from sheet1$ as x Thanks in advance, Noam ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try posting in the Security Forum, under SQL Server Security, that's where most people go when they have questions about SQL. ...

Multi-Select Listbox
I want to run a report using a multi-select listbox. The selected items would be the only ones that would come up on the report. Also it would be grouped on these selected item! Any direction is appreited. Thanks DS Here is a function I use for exactly that purpose. Private Function BuildWhereCondition(strControl As String) As String 'Set up the WhereCondition Argument for the reports Dim varItem As Variant Dim strWhere As String Dim ctl As Control Set ctl = Me.Controls(strControl) Select Case ctl.ItemsSelected.Count Case 0 'Include All strW...

Problem with Microsoft Query
It doesn't return some of the data. It returns all but one actually. And I went into Microsoft Query help and followed the instructions. The file is closed, and the data is formatted the same way. Any advice on how to fix it? Jul, Your question is extremely vague. You should probably provide more explicit information about the details of your query such as The type of file are you trying to query The program you are using to perform the query Details of the query parameters Details of the information that is not being retrieved Also, make sure you change the Subject of your messag...

power failure during Log commits
am trying to restore a mailbox. Got it to the point where the logs were committing when we had a power failure. Is there any way to "recommit the logs" from the point of the power failure or to start the commit again without having to go thru the steps to restore from tape and create new logs? Didn't Exchange resume the log replay? It should do that automatically. What version are you using? -- Blog "subject: exchange" http://spaces.msn.com/members/ehlo/ "Pearl" <Pearl@discussions.microsoft.com> wrote in message news:23282FB3-90B7-4F0E-944D-EB...

Toggle presenter view and full desktop copy
Consider the case where - besides a presentation - you'd also like to show live demos of some software. I really like presenter view as it helps a lot during the slide based part. However, once I come to the "and now for the demo" slide, I'd need a one click solution (preferred) to toggle between presenter view (with projector being used as "second monitor") and mirroring my desktop for the demo. Is there a way to achieve this? I wonder if you opt to run the presentation in a window, if you could easily drag it from one monitor to the other. Not the ea...

OpenSaveFile API and multi-select
I'm looking at the code here... http://www.mvps.org/access/api/api0001.htm how do you change the code to allow the selection of multiple files? (i'm just looking for an alternative to dumping all the files to a multi-select listbox for processing). Does someone have an example of how to return multiple files? How does one iterate through the files? Is it a collection, a delimited list? Or? Thanks Pieter ...

Multi Select List Not Working
The following code works perfectly for a multi select list. I've used it for several years on many applications. I'm encountering a strange error however on this code that takes criteria from three different lists and enters the data into a query. The first two list's criteria work fine but the third list has a strange quirk. Here's what it is doing. Let's say the third list has choices, 1,2,3,4,5,6. If I choose 3,4,5 in the list and run the code, it returns 1,4,5. If I choose 2,4,5,6 the code returns 1,4,5,6. It always returns the first value in my list wh...

Update query result to table
I can't find a good example on the web showing how to update table with the result from query. Table1 name "Agent1" column have: phone#(primary key), birthdate, and agent (total record 100). table2 name "Agent 2" have the same field (total record 150) except the data on agent is different. I run a query for this two tables and want to update the table1 with the table2 data where the phone#(primary Key). I want the result form the query will replace the agent in Agent1 table. How can i do that? UPDATE Table1 INNER JOIN Table2 ON Table1.[Phone#] = Table2.[Phone#]...

Open Form with macro and data from Selected Record
I am wanting a person to open a form, and have the subform display the records that relate to the main form. This part works. I then have a continuous form view for the subform and i want the user to be able to double click on the record they see in the subform and have it pull up that record into a blank form. I get the blank form to open up when they double click, however I cannot get the data for that record to show up. I either get a blank/new record or all records. I am trying the SearchForRecord macro in an On Load Event. This loads after the form has been opened. Ac...

Access 2007 Query of unique values
I am working with an ODBC connection to an SQL database. In attempting to create a simple query for unique values in a field, I would expect to right click in the quey and request properties. The only information that appears in the property sheeet is the data source name. Is it a problem with the ODBC connection? You need to know what tables and columns are in the database you are connecting to. I dont think Access can tell that since its not within the Access database. -Dorian "Norm" wrote: > I am working with an ODBC connection to an SQL database. In attempting to...

Query and Form to edit record
Hello Friends, So, I have a few tables: tblAgency Agency_ID = PK tblLOC LOC_ID = PK tblProgram Prm_Code = PK Agency_ID = FK LOC_ID = FK tblCensusEvent CensusEvent_ID = PK Prm_Code = FK CensusDate Census Discharges Admissions I have a great form with subform where I use a dropdown to choose the agency and it's program and then input the census, discharge and admission data for a specific date. Now I want a form where I can choose the Agency and it's Program in a similar dropdown and find that record with the census date, census, discharge and admission data and edit it if I n...

Formating query
I use excel as a database for storing / manipulating info for my web site which deals with bird checklists. The bird names are entered as follows... Common Buzzard (bold) space, Buteo buteo (italicised). My problem is that I have to merge the two tables (bold name) & (latin name in italics) into one cell but I cannot get the formating to stay. That is, the italicised name always follows the original cell format. Is there a symbol or control symbol I can insert between the bold and italic names to maintain the formating? I can change the format manually but with 20,000 entries to c...

Meaning of "last logged on by", Exch 2003 ?
On Exchange 2003, under "Mailbox Store", "Mailboxes", I see Mailbox:Smith, Joe Last Logged on By:"MyDomain\MyManager" I see a number of mailboxes in which "MyDomain\MyManager" appears as "last logon time" and last "logoff" time is the same, 8:00AM. Can someone explain why this happens ? What does that "last logged on by " really means ? the last user that accessed that particular mailbox will show up there. Philip Nunn "Marlon Brown" <marlon_brown@hotmail.com> wrote in message news:uv$x1tHCFHA.1524@...