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?

1 Answer 1


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:

EXEC XP_LOGININFO 'domain\groupname','members'
EXEC XP_LOGININFO 'domain\groupname','all'

This may be able to be wired up using a user-defined event.

  • Thanks Brian. I did two simultaneous traces, one for the group and the other for one of the users in the group and the results are consistent with your answer. Group returned nothing, but there were activities on the user login. Thanks for the suggestion as well, though I'd change the example from 'domain\groupname', 'all' to 'domain\username', 'all'. What I am gonna do is to trace a typical user in the group and base the permissions on that user. Nov 21, 2011 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.