Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  deutschZuid Nov 21 '11 at 22:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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