Powershell to get DB Role Members Report into Excel

Recently I had an application support group ask if I could get a report of all the members of the database roles that the application uses. I thought about it and, you know me, I thought, POWERSHELL!! Of course I can do that, I will get you a spreadsheet with that data as soon as I can. Off to Powershell I go and start thinking about how to do this.

1. Connect to the SQL Server instance
2. Connect to the database of interest
3. Gather all the database roles
4. Enumerate the members of the roles
5. Output to Excel in a readable format

That is the task and here is how I did it.


#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
#Counter variable for rows
$intcol = 1
connect-sql "Servername" ### See function Below
$db = $s.databases | where{$_.name -eq "Databasename"}
$roles = $db.roles | where{$_.Name -notlike "DB_*"}
foreach($role in $roles)
{
$rname = $role.Name
$Sheet.Cells.Item(1,$intcol) = $rname
$Sheet.Cells.Item(1,$intcol).Font.Bold = $True
$members = $role.EnumMembers()
$introw = 2
foreach($member in $members)
{
$Sheet.Cells.Item($intRow,$intcol) = $member
$introw = $introw + 1
}
$intcol = $intcol + 1
}
$Sheet.UsedRange.EntireColumn.AutoFit()


And here is the connect-sql function that I used.


function connect-sql ($arg)
{
if($arg -eq $null)
{
$server = Read-Host "enter instance name to connect to"
}
else
{
$server = $arg
}
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
}


hope that helps.