Hi All,

I found a way to query the Active Directory service to query the list of groups and the users assigned to that domain groups.

You can get the list of sysadmins on the box by querying as shown below :With the result from the query i was able to short list the users who fall under a particular Group (Group which has sysadmin role).Hope this would help someone.

SELECT p.name AS [loginname] ,

p.type , p.type_desc ,

p.is_disabled,

CONVERT(VARCHAR(10),p.create_date ,101) AS [created],

CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]

FROM sys.server_principals p

JOIN sys.syslogins s ON p.sid = s.sid

--WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')

-- Logins that are not process logins

AND p.name NOT LIKE '##%' -- Logins that are sysadmins

AND s.sysadmin = 1

Thanks

Nisha V Krishnan