Querying Active Directory Members for SQL Server Databases

This is a rather convoluted way of approaching the task required, but does also provide a degree of flexibility.

I needed to write a script to obtain a list of members of users and groups that have access to MS SQL Server databases. I know I could have just written a Powershell script and loop through the sys.server_principals table, but I chose to do it a bit differently. I basically wrote a SQL script that would generate a dynamic Powershell script that could be run as required. Perhaps a bit simplistic, but documenting it for my own future reference.

select 'Write-Host ""; Write-Host ' + name + '; Get-ADGroupMember -Identity ' + '''' + rtrim(substring(name,4,100)) + ''' -Recursive | Select Name; Write-Host ""' from master.sys.server_principals where type in ('G','U') and name like 'DOMAIN\%' and type_desc='WINDOWS_GROUP'
union
select 'Write-Host Direct Access: ' + name  from master.sys.server_principals where type in ('G','U') and name like 'DOMAIN\%' and type_desc='WINDOWS_LOGIN'