Know the Action Doer in Database

Question

Know the Action Doer in Database

shohelr2003

Hall of Fame

Points: 3535
More actions
July 8, 2013 at 10:01 pm

#279648

Dear,
In my database 'myDB', there are three logins as A, B, C. All are "sysadmin". And there is no restriction to access the database objects.
Say, A has done some modifications in a table without informing B and C. So how is the system to know which login or user has done the such modifications?
I am using SQL SERVER 2008 R2.
Please help me to know this.
Regards,
Akbar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

If it was recent enough you might be able to get it from the standard report of schema changes for the database. But there are no guarantees.

CEWII

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

shohelr2003 (7/8/2013)
Dear,
In my database 'myDB', there are three logins as A, B, C. All are "sysadmin". And there is no restriction to access the database objects.
Say, A has done some modifications in a table without informing B and C. So how is the system to know which login or user has done the such modifications?
I am using SQL SERVER 2008 R2.
Please help me to know this.
Regards,
Akbar

If you are trying to track changes on your database, you could implement SQL Auditing. You could also look at introducing triggers to track any changes.

shohelr2003 Hall of Fame Points: 3535 More actions · Answer 3

SQLSACT (7/9/2013)
If you are trying to track changes on your database, you could implement SQL Auditing. You could also look at introducing triggers to track any changes.

Thanks for your help. It helps me. SQL Auditing or Triggers, which is better?

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

shohelr2003 (7/9/2013)
SQLSACT (7/9/2013)
If you are trying to track changes on your database, you could implement SQL Auditing. You could also look at introducing triggers to track any changes.
Thanks for your help. It helps me. SQL Auditing or Triggers, which is better?

There isn't necessarily a "better" option. It depends on what exactly you need.

Have a look at the eventdata() - Some interesting stuff here

http://msdn.microsoft.com/en-us/library/ms173781.aspx

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

SQLSACT (7/9/2013)
shohelr2003 (7/9/2013)
SQLSACT (7/9/2013)
If you are trying to track changes on your database, you could implement SQL Auditing. You could also look at introducing triggers to track any changes.
Thanks for your help. It helps me. SQL Auditing or Triggers, which is better?
There isn't necessarily a "better" option. It depends on what exactly you need.
Have a look at the eventdata() - Some interesting stuff here
http://msdn.microsoft.com/en-us/library/ms173781.aspx

You also need to keep in mind that auditing may not be available in the edition of SQL being run.

I have used both and I prefer Audits, triggers work well but are more "visible" than I like, depending on how you do it there can also be permission and scope issues.

CEWII