Quick script to get all server and database roles from a given sql server instance. Marginally tested, use at your own risk.
function GetRoleMembers{ [CmdletBinding()] param( [Parameter(Mandatory=$True, ValueFromPipeline=$True)] [string[]]$serverInstance, [Parameter(Mandatory=$false, ValueFromPipeline=$false)] [string]$userName = $null, [Parameter(Mandatory=$false, ValueFromPipeline=$false)] [string]$password = $null ) BEGIN{ [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $outputs = @(); } PROCESS{ $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $srvConn.ServerInstance = $serverInstance $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn if($userName.Length -ne 0 -and $password.Length -ne 0){ $srvConn.LoginSecure = $false; $srvConn.Login = $userName $srvConn.Password = $password } else{ $srvConn.LoginSecure = $true; } #$s = $srv.EnumMembers([Microsoft.SqlServer.Management.Smo.RoleTypes]::All); foreach($role in $srv.Roles){ #[string[]] $roleMembers = $output = New-Object -TypeName PSObject -Property @{ ServerName = $serverInstance Database = '[Server Role]' Role = $role.Name Members = $role.EnumServerRoleMembers() #-join ', ' } $outputs += $output } foreach($database in $srv.Databases){ foreach($role in $database.Roles){ $output = New-Object -TypeName PSObject -Property @{ ServerName = $serverInstance Database = $database.Name Role = $role.Name Members = $role.EnumMembers()# -join ', ' } $outputs += $output } } } END{ $outputs | where{$_.Members -ne ''} | SELECT ServerName, Database, Role, Members } }