PoshCode Archive  Artifact Content

Artifact 277bf4dd08ddea336295b1a3b2dfa3e1fa68a4c1251785ef4401dec340fc5287:

  • File SQL-Server-Login.ps1 — part of check-in [afa204d20b] at 2018-06-10 13:40:57 on branch trunk — ############################################################################################# (user: Rob Sewell size: 3033)

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





}