DBA member updated the data. How to confirm that he did the update.
-
Hi,
The person who is responsible for the database (DBA member at client place) updated the particular data in table . How to confirm that he did the update and what time? User name? Machine name?.
we can create the trigger on all the tables but (DBA member at client place) can disable and update the data. (so this is not sufficient one)
we can create the Change Data Capture CDC on all the tables but it’s not storing the User name and Machine name . (so this is not sufficient one)
In case if we want to prove that the particular person did the update/Delete/ Insert on table without trigger. How to do that?
Is that a way we can read the .LDF (log file) and conform that? How to read the log file?
Thanks,
JK:-)
-
if you do not have a trace already in place, you cannot identify who did it;
you can read the log with some third party tools, but the log only has the changes and the time it occurred, not the whodunnit information...that has to be captured in a trace.
same for CDC i beleive, it has the changes, but not who changed what.
if you had a login trace running (which you also would have to have created beforehand) , you might be able to infer that sicne x number of people were connected, and the change occurred at [this time], it had to be one of thos3e x people...but again, it requires a pre-existing trace.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Apart from Trace file is there any other way we can do this. if the trace file running always then there will be performance issue correct?
Thanks
JK
-
running profiler has a huge impact, but a server side trace has minimal impact;
I have a DML/Login trace, kind of like the default trace, keeping track of the last 500 meg of changes, and never saw an impact, but it's helped enourmously when it's fingerpointing time and people are denying they did something.
here's a link to an article about the default trace's impact:
from Lowell from a long time ago
I'm in the same camp of not worrying about the trace impact after this article about the near-zero impact of a server side trace where someone actually compared the throughput compared to no trace vs profiler trace vs server side trace.take a look at how the graph from that article says the server side trace hardly affects the transactions per second: only when he starts emulating 300 users doing 1200 transactions per second do you see any deviance from no trace at all vs server side trace.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Nice one. Using the server side trace is there a way we can log the data only for perticular user. like the filter we set on SSMS trace file.
Thanks,
JK
-
jvskarthick (6/24/2011)
Nice one. Using the server side trace is there a way we can log the data only for perticular user. like the filter we set on SSMS trace file.Thanks,
JK
yes absolutely...you would use SQl Server Profiler to create the trace you want, with all the filters you want in place, then you simply do File>>Export>>Script Trace Definition>> For SQL Server 2005-2008.
you run the results of that file in SSMS to create that same trace serverside, without the huge overhead of Profiler.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
jvskarthick (6/24/2011)
Nice one. Using the server side trace is there a way we can log the data only for perticular user. like the filter we set on SSMS trace file.Thanks,
JK
Gawd, what this guy do? You say he works for your client?
---------------------------------------------------------------------
-
Note that you cannot be sure this will work. A DBA in control of the physical machine can always log in and remove logs, trace files, disable traces, etc. You might catch someone once, but they'll likely be looking for ways to disable things anonymously the second time.
That being said, the idea of using trace for limited events is a good one and has relatively low impact. However if someone says they made a change and didn't, can't you check data and handle this offline by contacting a manager at a client?
-
p.s. I think the tran log does record who made the change, so a log reading tool may work for you. their technical blurb should tell you.
Must be some interesting politics in play here. Hows the relationship with the client? 🙂
EDit : changed my mind, I know the log holds user info.
---------------------------------------------------------------------
-
Last I looked the transaction log recorded the SPID but not the login information, so we are back to if you aren't tracking that already you have nothing..
CEWII
-
beg to differ, for example:
http://www.apexsql.com/sql_tools_log.aspx
why would there be such tools if it wasn't for auditing purposes, to see WHO did what, so that information is in the log.
---------------------------------------------------------------------
-
george sibbald (6/24/2011)
beg to differ, for example:http://www.apexsql.com/sql_tools_log.aspx
why would there be such tools if it wasn't for auditing purposes, to see WHO did what, so that information is in the log.
george i'm pretty sure that info's available only if you already had Apex installed, as it does it's own trace that can then be tied into reading the transaction log; i'll google a bit to be sure, but that's seems to be how i remember it.
reading a trans log without that extra stuff they put in place just gives changes, is what i thought.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
did a bit of research and the transaction log holds the SID of the ID that issued the transaction, that can then be matched to the SIDS in the sysusers table of the database to find out the user.
If you run DBCC LOG (dbid\dbname,3) its the value in the transaction_sid column.
If the transaction is sa the SID will be 0x01. If however someone has access to the database by virtue of being in a group with sysadmin access the SID does not appear to link directly to an account.
---------------------------------------------------------------------
-
If this DBA changed data on the client's system - and is employed by the client, there really is not much you can do except work with the client's management to outline why this should never be done.
Really, the only recourse you have is to go back to your contracts with the client and show them that the contract explicitly states that changes made directly to the database system outside of the application is in violation of the contract.
If that does not exist in the contract - not really much you can do except to point out to the client why this shouldn't be done and what problems it causes. Inform the client that if this continues - support for the system will be charged back to the client. Of course, make sure your contract allows that - or you update the contract with the client so you are covered if these kinds of changes cause support issues.
As a DBA for a client where I support multiple vendor supplied applications - I am fully aware of what I can and cannot do for each application. In all cases, making changes directly to the database is not supported.
In cases where this needs to be done to fix issues, I always work with the vendor to validate the code and make sure there are no issues with performing these changes. Since this is a vendor supplied application, we either get the vendor to write the code for us so it is supported - or we write the code and submit it to the vendor to be validated and approved.
And, there is absolutely no changing of vendor supplied code - ever. If the vendor has the capability, you can clone their code (again, based on contract) and within the application specify the new code, or create a custom report using the new code and specify the custom report in the application. In these types of cases, the vendor will not support the new code - and upgrades become much harder because you have to reconcile all customizations with the upgrade.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Well George, I think you are right. I swear it wasn't always this way though. I remember looking at this exact problem a few years ago and realized we couldn't get the information we needed directly from the log. I guess this deficiency was resolved. Perhaps it was SQL 2000..
CEWII
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply