Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kwisatz
Starting Member
14 Posts |
 Posted - 2009-07-15 : 10:41:25
|
| Hi all - I am looking at auditing our SQL server instance and the use of SQL Trace. I am aware of the basic premise of creating a trace adding events to it, and also the space management issues. However one thing SQL Trace does not seem to do is to log user name i.e. should an user logging into an app make a change then the default SQL account is used. My question is how can I get SQL Trace to audit the user logged in at the app level. Is it possible to pass it the user via a CLR Trigger or something?I am just sounding ideas so anything is useful at this stage.Many thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 10:49:51
|
| You possibly could, but if you are using a single user to connect to SQL, it depends how your app users login. If there is a login in the app database, why not just set a trigger to write to an audit file the username used to login? The problem you have is that you could have multiple users logged in at the same time, yet you will not know which one is running what as all commands to the SQL database itself are through a generic user.In this situation, I would get the app itself to do the logging of the users as the app knows which session is open and which user made the call. |
 |
|
|
Kwisatz
Starting Member
14 Posts |
Posted - 2009-07-15 : 10:54:17
|
| OK I guess the developers could get the app to log which users have logged in, however how do I then link the user to the database commands they have executed? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 10:56:08
|
| Get them to keep a log of what is run by that user, this can be saved in a table in the same database if required. |
|
|
|
Kwisatz
Starting Member
14 Posts |
Posted - 2009-07-15 : 11:05:29
|
| right ok, I thought SQL Trace might be better than their current implementation of a CLR trigger which logs things to a table, but maybe not :( |
|
|
|
|
|
|
SQL Trace