We are currently undergoing an PwC audit on our database logins. Some of our user accounts have sysadmin privs, which of course is a major security breach. I have successfully done traces on individual user accounts, but my question is how do I trace a database login mapped to an AD Group? The said group has over two dozen users. I don't really care what access each individual in the group requires, but I do care about what the group needs as a whole, so I can give appropriate access before taking the sysadmin priv off it.
What I do for individual users right now is to select the appropriate events in the SQL Server Profiler and filtered the LoginName column with the name of the user surrounded by a pair of %. Can the same be done for AD group based logins? I am tracing the said group right now, but there doesn't seem to be any activity going on (could be that no one from the group is currently using the database). I just want to confirm that this is the right way to do it for this type of login, or am I missing something?
Best Answer
Unfortunately, the group is not presented in the LoginName column => it is the individual user account (DOMAIN\user).
You can get the groups for a given member by executing:
This may be able to be wired up using a user-defined event.