Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trace user in sql server Expand / Collapse
Author
Message
Posted Monday, June 16, 2014 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
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.
Post #1581037
Posted Monday, June 16, 2014 3:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 5,422, Visits: 10,078
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
Post #1581040
Posted Monday, June 16, 2014 4:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
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
Post #1581054
Posted Monday, June 16, 2014 4:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
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.
Post #1581056
Posted Monday, June 16, 2014 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:06 AM
Points: 5,422, Visits: 10,078
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
Post #1581058
Posted Monday, June 16, 2014 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 13,875, Visits: 28,272
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1581098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse