Determine the group a Windows Authentication user is authenticating as

[ESquared]

I figured out how to do this a month or two ago, and now I can't find it again.

Let's say I grant everyone in the Windows Security Group "Department A" general access to a server and to one database on that server.

Next, let's also say that I grant another Windows Security Group access to the server with more privileges. Some of the people in this group are in Department A and some are not.

Finally, let's say I distribute an application that connects via Windows Authentication to that server. If a security problem happens, to diagnose properly what the problem is, I need to know which group the person is authenticating as. For the people in Department A and the other group, it matters which they got in as (and this part isn't clear to me, anyway). Does anyone know?

Suser_sname() doesn't seem to do it. I tried it on a server that I have access to only through group membership, but this function returns my username there, not the group I'm a member of.

I know this is possible as I did it before, but all my web searches are failing because of all the extra search results about random Windows and SQL Server security.

 

[Jamfool]

could you not use:

--

http://msdn.microsoft.com/en-us/library/ms186271.aspx

-- Execute SELECT if user is a member of ADVWORKS\Shipping.
IF IS_MEMBER ('ADVWORKS\Shipping') = 1
SELECT 'User ' + USER + ' is a member of ADVWORKS\Shipping.'
go