This recipe shows how you can list a login- and user-related roles and permissions.
Let's check the code needed to list the login/user roles and permissions.
Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.
Import the
SQLPS
module and create a new SMO Server object as follows:#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName = "KERRIGAN" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Add the following script and run it:
$server.Databases | ForEach-Object { #capture database object $database = $_ #capture users in this database $users = $_.Users $users | Where-Object { -not($_.IsSystemObject)} | Select @{N="Login";E={$_.Login}}, @{N="User";E={$_.Name}}, @{N="DatabaseName";E={$databaseName}}, @{N="DBRoles";E={$_.EnumRoles()}}, ...