The Multifunctioning DBA

Aug 21 2012   4:29PM GMT

Powershell to get DB Role Members Report into Excel

Colin Smith Colin Smith Profile: Colin Smith

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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: