March 9, 2009 at 4:28 am
HI All,
We are building a application for users to make changes to the DB so we are building in some audit functionality to record who is doing what. The guy that is doing the application has been away so i was wondering whether there was any way that i can capture the log in information from SQL.
ideally the Pc name, IP address or windows log in would be excellent. Just so we have some come back if someone screws up the DB!
Thanks in advance for your help 😀
March 9, 2009 at 9:02 am
You can actually use Default trace from SQL 2005 to capture the info you are looking for. Alternately, You can write Alter triggers. I got this info from GSquared. 🙂
With default trace, make sure that you write it to a table because it has a very short life span. Try a search in this site itself, You will see quite a few threads regarding this.
-Roy
March 9, 2009 at 10:52 am
Yes as suggested Default trace will be right tool, load information into a table as it traces the events. You can look at: fn_trace_gettable function in BOL which extracts event information from the trace file to the table.
March 9, 2009 at 10:57 am
Hi,
Thanks to you both for taking the time to reply to me. I have looked at the table that this returns :
SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
and it doens't appear to be updating with recent activity. I have checked that it is enabled and i think that it is. I am a little confused. Any suggestions?!
Thanks again to you both
Regards,
Matt
March 9, 2009 at 11:04 am
Is your default trace enabled?
check out this beginner's guide to default trace:
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
March 10, 2009 at 3:18 am
thanks for the link. I have looked over the document. It appears that the default trace is a very useful tool...hopefully i can get it working 😀
It appears to be enabled. Please see attachment.
Many, Many thanks! 🙂
Matt
March 10, 2009 at 4:33 am
I think that i have got this working. The server date is a couple of weeks off..this was throwing me off from thinking that it was returning old data!
Unfortunately i dont think that its going to be good enough for what i wanted. It doesn't seem to be recording when a Sproc is executed, and the result of the sproc being executed (i.e. which tables are affected)
Thanks again for all your help though. I am sure this tool will be useful in the future for other things. Its good to know its there 😀
Best Regards,
Matt
March 10, 2009 at 6:33 am
If you want to see who is executing what Stored Proc or SQL statements, you can just grab the info that you get on the profiler and dump it into a table. Please note that the table (DB) that you store this data could get very big depending on how busy your SQL Server is.
-Roy
March 10, 2009 at 6:47 am
Surely i would have to set up a trace that ran in the background all day looking for the specific details. Then dump these into a table manually at the end of the day?! There is no way to automate this is there?
I think that overall this is going to be easier to gather this information directly from when the application runs. The .net code will be able to pass what is being executed, and what it is affecting. Some of this infomation can be passed from the proc itself.
Thank you very much for your help tho. I appreciate you taking the time to reply.
Matt
March 10, 2009 at 6:56 am
No, You can automate it. If you just search for setting up trace in this site itself, I am sure you will get some articles about it.
-Roy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply