Last week Linchi Shea wrote a posted a blog entry entitled “How does that AD user account get access to the database?”. In it Linchi shows a method of finding out the domain groups that a SQL Server user uses to access the database.
There is however an easier, method you can use.
There is a system extended stored procedure called xp_logininfo. Microsoft was even kind enough to document the procedure for us. You can use this procedure to see what group a use belongs to, or what users are in a domain group, all from T/SQL.
For example, on my sandbox instance if I run
[sql]EXEC xp_logininfo ‘CORPdcherry'[/sql]
I get a result set which says that I gained my access to the SQL Server via the “BUILTINAdministrators” group.
This is a nice quick and easy method to see what domain group a user used to access the SQL Server.
Denny