Going through your databases to find and clean up users with no associated server logins can be a pain.  It is fairly easy to find scripts that will go out and find those users, and even some will drop those users automatically.  Unfortunately, sometimes it is not so simple to just drop a user from a database because they own one or more schema.

To drop these users from a database the schema owned by that user must be altered to remove the dependency.

Thus I set to work to come up with a powershell script to identify users with no associated login, the schema that need to be altered to enable the dropping of those users and create a document logging the changes to be made.

Here is the script I came up with:

$servername = 'localhost' #change to the name of the server you are interested in
$outputfile = 'C:\localhost modifications.csv'
$targetowner = 'dbo'
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
 
$s = new-object ("Microsoft.SqlServer.Management.SMO.Server") $servername
 
#Uncomment this section if you want to use SQL Authentication
 
#$s.connectioncontext.loginsecure = $false $cred = get-credential
#$s.connectioncontext.set_Login($($cred.username.replace("\","")))
#$s.connectioncontext.set_securepassword($cred.password) #>
 
$dbs = $s.databases | ?{-not $_.IsSystemObject}
 
#explicitly declare the collection variables as arrays
[Microsoft.SqlServer.Management.Smo.User[]]$users = $Null
[Microsoft.SqlServer.Management.Smo.Schema[]]$schemas = $Null
[PSObject[]]$output = $Null
 
#Get the users to drop and all schema owned by those users
$dbs | %{
$db = $_
[Microsoft.SqlServer.Management.Smo.User[]]$dbusers = $db.users | ?{$_.Login -eq '' -and -not $_.IsSystemObject}
$users += $dbusers
[string[]]$OrphanSchema = $Null
If ($dbusers.count -gt 0) {
$dbusers | %{$OrphanSchema += $_.EnumOwnedObjects()}
$schemas += $db.Schemas | ?{$OrphanSchema -contains $_.urn}
}
}
If ($schemas.count -gt 0) {
$output += $schemas | SELECT @{Name='Database';Expression={$_.Parent}}, Name, @{Name='Type';Expression={'Schema'}}, @{Name='Action';Expression={"Change owner from $($_.Owner) to $targetowner"}}
}
If ($Users.count -gt 0) {
$output += $Users | SELECT @{Name='Database';Expression={$_.Parent}}, Name, @{Name='Type';Expression={'User'}}, @{Name='Action';Expression={"Drop"}}
}
If ($output -eq $Null) {
$output = ''
}
$output | export-csv $outputfile -Force -notype
 
#uncomment these lines when you are ready to perform the modifications
#$schemas | %{$_.owner = $targetowner; $_.Alter()}
#$users | %{$_.Drop()}

Initially, this script should be run with the last two lines commented out to generate a csv file of all the schema the script will change and all of the users it will drop. Once you feel confident that this script will do exactly what you want it to, these lines can be uncommented to execute the changes.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Credit for this fantastic disclaimer goes to Buck Woody (Blog | Twitter).