0

I know you can see when tables or views were last modifed in sys.tables or sys.views in SQL Server.

It does not appear there is a table like this for users or logins --- or is there?

Basically, a user is having permissions issues and --- I can see their current permissions --- but I would like to know when it was last "changed" and possibly by "whom" --- is that possible retroactively?

user45867
  • 1,683
  • 5
  • 22
  • 38
  • Only way I can think of retro is to restore a copy of your database(s) to another server from a backup before the changes happen. Going forward you could maybe log this to a table or just create a trigger on the logon for future use. – S3S Aug 15 '19 at 16:27
  • @scsimon this comment looks like it should be posted as an answer. – James Jenkins Aug 15 '19 at 16:40
  • 2
    @scsimon a logon trigger is a trigger that runs when a log in operation ("log on"), happens, not when a login is changed. – Aaron Bertrand Aug 15 '19 at 16:59
  • 2
    You can capture some recent login/user change activities, like adding them to a role or changing some properties, using the default trace: SELECT * FROM sys.fn_trace_gettable((SELECT path FROM sys.traces WHERE is_default = 1), DEFAULT) WHERE EventClass IN (102,103,105,106,108,110); ... but the default trace does not capture more granular things like assigning individual permissions, and its content ages out, so it is only present for a limited time. – Aaron Bertrand Aug 15 '19 at 17:34

1 Answers1

1

You can have a look at

  1. Server audit logging
  2. C2 Audits

When working at banks or some Government agencies one Normally needs some way to proof who has accessed the database as well as the reports that was generated.

What happens is one always has to log the machine and user name the user used to accessed the data. one can record this by caling SUSER_SNAME() and Host_Name() in your procedures and logging this data along with the parameters used so that one can verse engineer who has accessed/ seen what data.

Walter Vehoeven
  • 166
  • 1
  • 6