June 16, 2014 at 3:34 am
Hi All,
Can anyone please help me to trace the user , if someone has deleted the records or table from the database or inserted or done any modification in database .
From where can I trace the user if the user is working on sql server authentication, because everyone, profiler will display the same user.
June 16, 2014 at 3:41 am
If you don't have any auditing set up then you're not going to be able to find out much. For DDL changes, you can try the default trace.
If you have several people connecting under the same login, it's going to be even more difficult. You may be able to distinguish individuals by the host or application they connected from.
John
June 16, 2014 at 4:31 am
Hi John thanks for the reply,
hostname will be showing for app server not for individual user. is there any alternative solution or query which is helpful for me
June 16, 2014 at 4:33 am
what does it mean "If you don't have any auditing set up then you're not going to be able to find out much."
sorry i am new to this activity.
June 16, 2014 at 4:35 am
You can set up your own auditing on your database, for example using triggers to write to audit tables whenever any changes are made to data in any tables. Or maybe your application does its own auditing?
John
June 16, 2014 at 6:39 am
There are a number of tools you can set up to track changes to the system. If you want to track individual queries you can use extended events or trace. If you want to see data tracked you can set up change data capture. As was already said you can set up triggers to capture modifications.
But, you're right. There is no way to differentiate a given user if everyone is using a common logon. If you can modify the application code so that it has to also pass in the user name, then you could capture that using one or more of the mechanisms above. But, barring that, you're going to be largely stuck.
Architectural choices really do matter. Choosing to use a SQL login absolutely takes away certain types of functionality.
Hmmm... you can capture the client host name. If a person is connecting from their own machine, you could get that at least. But if they're connecting through a server that won't help either.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply