# 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 "#########################################" }