# encoding: ascii # api: powershell # title: SQL Server Login # description: ############################################################################################# # version: 0.1 # type: function # author: Rob Sewell # license: CC0 # function: Show-SQLUserLogins # x-poshcode-id: 4431 # x-archived: 2014-08-18T21:33:27 # x-published: 2014-09-01T09:52:00 # # # # NAME: Show-SQLUserLogins.ps1 # AUTHOR: Rob Sewell http://sqldbawiththebeard.com # DATE:06/01/2013 # # # COMMENTS: Load function to Display a list of server, database and login for SQL servers listed # in sqlservers.txt file from a range of domains # ############################################################################################# # # NAME: Show-SQLUserLogins.ps1 # AUTHOR: Rob Sewell http://sqldbawiththebeard.com # DATE:06/01/2013 # # COMMENTS: Load function to Display a list of server, database and login for SQL servers listed # in sqlservers.txt file from a range of domains # # USAGE Show-SQLUserLogins DBAwithaBeard Function Show-SQLUserLogins ($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 "SQL Servers, Databases and 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`n" foreach($server in $servers) { $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server foreach($login in $logins) { if($srv.Logins.Contains($login)) { Write-Host " $server , $login " } else { } } } Write-Host "`n#########################################" Write-Host "`n Database Logins`n" 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 " $server , $database , $login " } else { continue } } } } Write-Host "`n#########################################" Write-Host "Finished - If there are no logins displayed above then no logins were found!" Write-Host "#########################################" }