PoshCode Archive  Artifact Content

Artifact 3db97546ff5ad71bd797c92d7091722def17bc9f0e3c61f69bdbb504ee69d33b:

  • File SQL-User-Permissions.ps1 — part of check-in [01737ec7d5] at 2018-06-10 13:40:59 on branch trunk — ############################################################################################# (user: Rob Sewell size: 4206)

# encoding: ascii
# api: powershell
# title: SQL User Permissions
# description: #############################################################################################
# version: 0.1
# type: function
# author: Rob Sewell
# license: CC0
# function: Show-SQLUserPermissions
# x-poshcode-id: 4433
# x-archived: 2016-10-09T19:44:33
# x-published: 2013-09-01T10:25:00
#
# #
# NAME: Show-SQLUserPermissions.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:06/08/2013
# #
# COMMENTS: Load function to Display the permissions a user has across the estate
# NOTE – Will not show permissions granted through AD Group Membership
# USAGE Show-SQLUserPermissions DBAwithaBeard
#

#############################################################################################
#
# NAME: Show-SQLUserPermissions.ps1
# AUTHOR: Rob Sewell http://sqldbawiththebeard.com
# DATE:06/08/2013
#
# COMMENTS: Load function to Display the permissions a user has across the estate
# NOTE - Will not show permissions granted through AD Group Membership
# 
# USAGE Show-SQLUserPermissions DBAwithaBeard


Function Show-SQLUserPermissions ($usr)
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Suppress Error messages - They will be displayed at the end

$ErrorActionPreference = "SilentlyContinue"
#cls

# Pull a list of servers from a local text file

$servers = Get-Content 'c:\temp\sqlservers.txt'

# Create an array for the username and each domain slash username

$logins = @("DOMAIN1\$usr","DOMAIN2\$usr", "DOMAIN3\$usr" , "$usr")

				Write-Host "#################################" 
                Write-Host "Logins for `n $logins displayed below" 
                Write-Host "################################# `n" 

	#loop through each server and each database and display usernames, servers and databases
       Write-Host " Server Logins"
         foreach($server in $servers)
{
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
    		foreach($login in $logins)
		{
    
    			if($srv.Logins.Contains($login))
			{

                                    Write-Host "`n $server , $login " 
                                            foreach ($Role in $Srv.Roles)
                                {
                                    $RoleMembers = $Role.EnumServerRoleMembers()
                                    
                                        if($RoleMembers -contains $login)
                                        {
                                        Write-Host " $login is a member of $Role on $Server"
                                        }
                                }

			}
            
            else
            {

            }
         }
}
      Write-Host "`n#########################################"
     Write-Host "`n Database Logins"               
foreach($server in $servers)
{
	$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
	foreach($database in $srv.Databases)
	{
		foreach($login in $logins)
		{
			if($database.Users.Contains($login))
			{
                                       Write-Host "`n $server , $database , $login " 
                        foreach($role in $Database.Roles)
                                {
                                    $RoleMembers = $Role.EnumMembers()
                                    
                                        if($RoleMembers -contains $login)
                                        {
                                        Write-Host " $login is a member of $Role Role on $Database on $Server"
                                        }
                                }
                    

			}
                else
                    {
                        continue
                    }   
           
		}
	}
    }
   Write-Host "`n#########################################"
   Write-Host "Finished - If there are no logins displayed above then no logins were found!"    
   Write-Host "#########################################" 





}