Listing login/user roles and permissions

This recipe shows how you can list login and user related database mappings, roles, and permissions for a specific database.

How to do it...

Let's check the code needed to list logins, their database mappings, assigned roles, and permissions:

  1. Open PowerShell ISE as an administrator.
  2. 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 = "localhost"
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

    Add the following script and run:

    #list all databases you want to query in an array #alternatively you can read this from a file $databases ...

Get SQL Server 2014 with PowerShell v5 Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.