| Author |
Topic |
|
garipi82
Starting Member
4 Posts |
 Posted - 2011-10-27 : 04:50:58
|
| Hello everyone,Is any possibilities to trace the SQL users which are making some queries for exc: Update, Insert, Delete. I mean that in case one user have don a query UPDATE to find which is the user, the row of the table which hi/she has don this update and the cell which is updated and what was there before updating and what is after updating.Regards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 04:58:19
|
| you need to run sql profiler trace for that and filter based on user name------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 04:58:47
|
| for getting before update and after update values use triggers------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
garipi82
Starting Member
4 Posts |
Posted - 2011-10-27 : 05:26:56
|
| Thank you, i tried triggers but i can not find what is modified and what was the original cell which is modified, can you give me an trigger exc for this. Please.Regards |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 06:09:12
|
| can you post your tried trigger? you should be using COLUMNS_UPDATE function to identify columns that took part in update and use INSERTED and DELETED tables to get value before and after update------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
garipi82
Starting Member
4 Posts |
Posted - 2011-10-27 : 06:16:41
|
| set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [dbo].[pstdevHIS] ON [dbo].[PSTDEV] FOR UPDATEASif system_user<>'Banka'begininsert into pstdev_history select 'UPDATE',host_name(),system_user,getdate(),* from deletedend--select * from pstdev_history |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
human12
Starting Member
5 Posts |
Posted - 2011-10-27 : 07:21:56
|
| I believe continuous running SQL profile will make lots of problems to your database,Monitoring the actions through triggers, and creating sql login for each user who is going to update, to catch his usernameI also believe this will be a headache for the serveryou can check the new OUTPUT operator introduced in SQL 2005, it can help u big with tracking the changes through triggers as explained herehttp://intosql.blogspot.com/2011/10/sql-server-2005-output-operator.html?utm_source=BP_recentTarek OmarTechnical Manager and Database Administratorhttp://intosql.blogspot.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 07:26:46
|
quote:
Originally posted by human12
I believe continuous running SQL profile will make lots of problems to your database,Monitoring the actions through triggers, and creating sql login for each user who is going to update, to catch his usernameI also believe this will be a headache for the serveryou can check the new OUTPUT operator introduced in SQL 2005, it can help u big with tracking the changes through triggers as explained herehttp://intosql.blogspot.com/2011/10/sql-server-2005-output-operator.html?utm_source=BP_recentTarek OmarTechnical Manager and Database Administratorhttp://intosql.blogspot.com
thats fine. but how will ensure this will capture all the data modifications happening? unless you include code using OUTPUT everywhere you cant capture all modifications. for example, if somebody does an ad-hoc update unless (s)he includes code with OUTPUT it wont capture details. Trigger though has some overhead provides you with a centralised option to capture DML actions. Once created, you dont have to worry on how you will capture required details for each and every DML operation done------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
human12
Starting Member
5 Posts |
Posted - 2011-10-27 : 08:56:19
|
quote:
Originally posted by visakh16
quote:
Originally posted by human12
I believe continuous running SQL profile will make lots of problems to your database,Monitoring the actions through triggers, and creating sql login for each user who is going to update, to catch his usernameI also believe this will be a headache for the serveryou can check the new OUTPUT operator introduced in SQL 2005, it can help u big with tracking the changes through triggers as explained herehttp://intosql.blogspot.com/2011/10/sql-server-2005-output-operator.html?utm_source=BP_recentTarek OmarTechnical Manager and Database Administratorhttp://intosql.blogspot.com
thats fine. but how will ensure this will capture all the data modifications happening? unless you include code using OUTPUT everywhere you cant capture all modifications. for example, if somebody does an ad-hoc update unless (s)he includes code with OUTPUT it wont capture details. Trigger though has some overhead provides you with a centralised option to capture DML actions. Once created, you dont have to worry on how you will capture required details for each and every DML operation done------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Total AgreeTarek OmarTechnical Manager and Database Administratorhttp://intosql.blogspot.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
SQL AUDITING