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
}
}
Leave a comment