ron there's a built in extended procedure that can tell you what Ad groups an AD login belongs to.

the problem is, you do NOT want to grant everyone access to extended stored procs.

the syntax is simple:

EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all' -- Show all paths a user gets his auth from

go

EXEC master..xp_logininfo @acctname = 'mydomain\authenticatedusers',@option = 'members' -- show group members

since in reality, people change/get added/drop from groups very rarely, i would suggest scanning a list of users and put all their groups into a single static table;

then use that table for your test, and update it once a week or so.

here's a very basic example;

CREATE TABLE [dbo].[#TMP] (

[ACCOUNT NAME] NVARCHAR(256) NULL ,

[TYPE] VARCHAR(8) NULL ,

[PRIVILEGE] VARCHAR(8) NULL ,

[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,

[PERMISSION PATH] NVARCHAR(256) NULL )

INSERT INTO #tmp

EXEC master..xp_logininfo @acctname = 'mydomain\lowell',@option = 'all'

IF EXISTS(SELECT 1 FROM [dbo].[#TMP] WHERE [account name] = ORIGINAL_LOGIN() AND [permission path] = 'BUILTIN\Administrators')

PRINT 'whoopee!'

--all inline as a single command:

IF EXISTS(SELECT 1 FROM (SELECT *

FROM OPENROWSET('SQLOLEDB','Server=DEV223\SQL2005;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC master..xp_logininfo @acctname = 'mydomain\lowell'',@option = ''all'' '

)

) MyAlias

WHERE [account name] = ORIGINAL_LOGIN()

AND [permission path] = 'BUILTIN\Administrators')

PRINT 'whoopee!'