Get all server and database roles

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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.