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:
Open PowerShell ISE as an administrator.
Import the
SQLPSmodule 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 = @("AdventureWorks2014") #we will temporarily store results in an array #so it will easier to read and export $results = @() $databases | ForEach-Object { #capture current database object $database = $server.Databases[$_] #capture...