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.
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".