How many times have you wished you could use SQL Server to query Active Directory and see who is in that group? This week’s script will allow you to do that, so if you have AD groups set up for logging into your server you can see exactly which logins have access.
xp_logininfo is an extended stored procedure that does just that. By passing a couple parameters, you can easily query any AD group that is a server principal. Wrap it up in a dynamic SQL loop, and you can quickly and easily audit your server’s security.
declare @winlogins table (acct_name sysname, acct_type varchar(10), act_priv varchar(10), login_name sysname, perm_path sysname) declare @group sysname declare recscan cursor for select name from sys.server_principals where type = 'G' and name not like 'NT%' open recscan fetch next from recscan into @group while @@FETCH_STATUS = 0 begin insert into @winlogins exec xp_logininfo @group,'members' fetch next from recscan into @group end close recscan deallocate recscan select r.name, u.name, u.type_desc, wl.login_name, wl.acct_type from (select * from sys.server_principals where type = 'R') r join sys.server_role_members rm on (r.principal_id = rm.role_principal_id) join (select * from sys.server_principals where type != 'R') u on rm.member_principal_id = u.principal_id left join @winlogins wl on u.name = wl.perm_path order by login_name,r.principal_id,u.type_desc,u.name
Looking at the script, I first do a looped scan through sys.server_principals for all Windows groups (type ‘G’) and collect their AD group info. I store that in a table variable, then join it back to a select from server_principals again for a full listing of all my server permissions. Bam! Instant security report.