SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trace user in sql server


Trace user in sql server

Author
Message
arooj300
arooj300
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 533
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14269 Visits: 15974
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
arooj300
arooj300
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 533
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
arooj300
arooj300
SSC Veteran
SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)SSC Veteran (297 reputation)

Group: General Forum Members
Points: 297 Visits: 533
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14269 Visits: 15974
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40315 Visits: 32665
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search