PoshCode Archive  Artifact [bf2d82a13a]

Artifact bf2d82a13a8054794b72d0de743f403244888f3c418f1bdbaf246f36bf630159:

• File Drop-SQL-Users.ps1 — part of check-in [3a67e9c1cd] at 2018-06-10 13:41:07 on branch trunk — NAME: Drop-SQLUsers.ps1 (user: Rob Sewell size: 3269)

# encoding: ascii
# api: powershell
# title: Drop SQL Users
# description: NAME: Drop-SQLUsers.ps1
# version: 0.1
# type: function
# author: Rob Sewell
# license: CC0
# function: Drop-SQLUsers
# x-poshcode-id: 4449
# x-archived: 2014-08-18T21:33:32
# x-published: 2014-09-09T12:27:00
#
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:06/08/2013
# #
# COMMENTS: Load function to Display a list of server, database and login for SQL servers listed 
# in sqlservers.txt file and then drop the users
# #
# I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped
# #
# Does NOT drop Users who have granted permissions
#

#############################################################################################
#
# NAME: Drop-SQLUsers.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:06/08/2013
#
# COMMENTS: Load function to Display a list of server, database and login for SQL servers listed 
# in sqlservers.txt file and then drop the users
#
# I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped
#
# Does NOT drop Users who have granted permissions
#BE CAREFUL


Function Drop-SQLUsers ($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 "Dropping Logins for $Logins" 


	#loop through each server and each database and 
     Write-Host "#########################################"
     Write-Host "`n Database Logins`n"  
foreach($server in $servers)
{      	
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	#drop database users
	foreach($database in $srv.Databases)
	{
		if ($database -notlike "*dontwant*")
        {
            foreach($login in $logins)
		      {
			 if($database.Users.Contains($login))
			 {
			 	$database.Users[$login].Drop();
                 Write-Host " $server , $database , $login  - Database Login has been dropped" 
			 }
		      }
	   }
    }
    }
    
     Write-Host "`n#########################################"
     Write-Host "`n Servers Logins`n" 
      
    foreach($server in $servers)
{      	
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	#drop server logins
	foreach($login in $logins)
	{
		if ($srv.Logins.Contains($login)) 
		{ 
			$srv.Logins[$login].Drop(); 
         Write-Host " $server , $login Login has been dropped" 
		}
	}
}
Write-Host "`n#########################################"
Write-Host "Dropping Database and Server Logins for $usr - Completed "  
Write-Host "If there are no logins displayed above then no logins were found or dropped!"    
Write-Host "###########################################" 
}