Dear Reader,

Few days ago, I was looking for a script to find out who all have access to SQL Server via AD Groups. There are a couple of good scripts available for this task. However I thought of re-inventing the wheel. You are free to modify and re-share it.

This script works with SQL Server 2005, 2008 and 2012.

/* Written by Sachin Diwakar for www.dbathings.com*/

CREATE TABLE #holdLogins (
 accountname VARCHAR(max) 
 ,type VARCHAR(20)
 ,priv VARCHAR(20)
 ,mappedlogin VARCHAR(max)
 ,ADGroup VARCHAR(max) )
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @user_name VARCHAR(100)
,@exec_sql VARCHAR(2000)
DECLARE user_cursor CURSOR FOR
SELECT NAME FROM sys.syslogins WHERE (isntname = 1 AND isntgroup = 1 ) AND ( NAME NOT LIKE 'NT SERVICE%' and name not like 'NT AUTHORITY%')
-- and NAME ='MyDomain\SQL Admins' 
-- find out users of individual groups if you want to.
OPEN user_cursor
FETCH NEXT
FROM user_cursor
INTO @user_name 
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @exec_sql = 'EXEC master..xp_logininfo @acctname = ''' + @user_name + ''',@option = ''members'''
BEGIN TRY 
 -- print @exec_sql 
 
 INSERT INTO #holdLogins 
 EXECUTE (@exec_sql) 
END TRY
BEGIN CATCH
 INSERT INTO #holdLogins
 VALUES ( '--NA--'
 ,'--NA--' 
 ,'--NA--' 
 ,'--Error has occured--' 
 ,@user_name 
 )
 END CATCH
FETCH NEXT
 FROM user_cursor
 INTO @user_name
END
CLOSE user_cursor
DEALLOCATE user_cursor
SELECT * FROM #holdLogins
DROP TABLE #holdLogins

I hope this helps..