How to get index usage information in SQL Server (PowerShell)

Introduction

This script will output three parts, one to get the result of server level logins and related roles, one to get the result of user databases’ users and related roles, one to get the result of object level permission of specific database.

Scenarios

This script can be used to get the following results:

Server level logins and related roles
User databases users and related roles
Object level permission of specific database

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator

2. Run the script in the form: &Path
For example: &"D:\Syncup\OneScript\Julie\GetLoginUserRolePermission.ps1"

3. Press Enter and enter the server name and database name.
Note: server name should be in the format: Server Name\ Instance Name. If it's a default instance, please enter the server name.

4. Press Enter and choose between Windows and SQL authentication. If you choose SQL Server authentication, please enter the correct user name and password.

5. When the script finishes running, we抣l get the following figure:

Here are some code snippets for your reference:

PowerShell
Edit|Remove
powershell#Create SQLDataAdapter object with command text and connection
$SQLDataSet = New-Object System.Data.DataSet
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection)          
$SQLAdapter.Fill($SQLDataSet) | Out-Null           
#To show the result
Write-Host "--------------------------"
Write-Host "Databases users and roles "
Write-Host "-------------------------"
$SQLDataSet.Tables[0]| Format-List
Write-Host "--------------------------------------------------"
Write-Host "Get objects permission of specified user database "
Write-Host "--------------------------------------------------"
$SQLDataSet.Tables[1]| Format-List
Write-Host "----------------------------------------------------------------"
Write-Host "Get objects permission of specified user database:$DatabaseName "
Write-Host "----------------------------------------------------------------"
$SQLDataSet.Tables[2]| Format-List

#Create SQLDataAdapter object with command text and connection 
$SQLDataSet = New-Object System.Data.DataSet 
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLQuery,$SQLConnection)           
$SQLAdapter.Fill($SQLDataSet) | Out-Null            
#To show the result 
Write-Host "--------------------------" 
Write-Host "Databases users and roles " 
Write-Host "-------------------------" 
$SQLDataSet.Tables[0]| Format-List 
Write-Host "--------------------------------------------------" 
Write-Host "Get objects permission of specified user database " 
Write-Host "--------------------------------------------------" 
$SQLDataSet.Tables[1]| Format-List 
Write-Host "----------------------------------------------------------------" 
Write-Host "Get objects permission of specified user database:$DatabaseName " 
Write-Host "----------------------------------------------------------------" 
$SQLDataSet.Tables[2]| Format-List

Prerequisites

SQL Server 2005 /SQL Server 2008/SQL Server 2008 R2/SQL Server 2012

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.