My users login to SQLServer through Active Directory security group memberships.  These groups are security groups in SQLServer and used to assign permissions to database objects.

My problem is that when I query system_user, current_user, suser_sname and user I get the individual user, not the group they are a member of.

So where they login in through 'DOMAIN\SecurityGroup' I get back 'DOMAIN\UserName'

When I try User_ID and user_name I get back the SQLServer role like 'dbo' and 'public'.

How can I detemine which 'DOMAIN\SecurityGroup' is being used by 'DOMAIN\UserName' to access the database.

Thanks EdVassie, but that does not really address the issue.  I don't have a problem with internal SQL security on objects.

The issue is the delegated permission from Active Directory.  I want to know the DOMAIN\SecurityGroup with which the DOMAIN\User has gained access to the database.  As a work around I have a mapping table in my database that maps Active Directory users to their groups.  This can be handled by .NET in the front end, but I won't even mention the legacy platform I'm working with here.

If I know the  DOMAIN\SecurityGroup, which I have used for login and object permissions, then I can query sp_helprotect and determine what permissions the current user has on the object, and hence what controls to enable.  Otherwise I would have to wait for a fail on commit, which would be annoying for the user as they would not know until the end of a process whether it will succeed. 

2 issues

1st....can a user belong to more than 1 AD security group...(for different reasons).  Technically I think the answer would be yes....so I think you need to cater for looping through multiple results.

2nd....you should be looking to build an AD lookup/interface routine to execute this functionaility...I've seen others point to resources for doing such actions....search for "AD, SQL, Lookup/interface" and see if anything useful comes up.  I don't think it's a native SQL "system variable".