Unless you've got a well-defined strategy to manage SQL Server logins and database users via Active Directory groups (as I usually recommend when setting up SQL Server security), you're going to eventually find yourself in a situation where you'll need to clean up logins that don't have access to any database and database users that don't have an associated login. Usually, I find this in new clients where, after cleaning up database maintenance practices, security is the next step in getting things under control.
Related: Run Your SQL Server DBCCs and Check Your Errorlog with PowerShell
In the SQL Server Management (SMO) object model, you connect to a SQL Server instance by creating a Server object. One of the collections in that Server object is the Logins collection, and that contains a set of Login objects, one for each login defined on the instance.
You can look at the properties of the Login object by piping the object to the Get-Member cmdlet.
You can exclude system logins by testing the IsSystemObject property for $True, and use the Name and ID properties in verifying our security settings.
Similarly, you can iterate through the Databases collection and get the set of users in each database.
What's interesting is that you can then compare the Login property of the database User object to the Name property of the Login object to connect a SQL login and its associated database users.
To clean up logins that: a) aren't system logins; b) aren't members of the sysadmin server role; and c) don't have any database users associated with the login, you'll first populate a collection of the qualifying logins.
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
$logins = $srv.Logins
$log = @()
foreach ($l in $logins) {
$lnm=$l.Name
if (!($l.IsMember('sysadmin')) -and ($l.IsSystemObject -eq $False)) {
$lg=new-object system.object
$lg | Add-Member -type NoteProperty -name Name -value $lnm
$log += $lg
}
}
Next, you'll create another collection containing the logins that don't have any database users. Do this by iterating through the collection of qualifying logins, and then looking into the Users collection in each database for a User object where the User Login property matches the Name property of the Login. If you find one (by evidence that we get a valid ID property for the User object), set the $lgused variable to $true. If, after you've cycled through the databases, the variable is still set to $False, add it to the $nodb collection.
$nodb = @()
foreach ($lg in $log) {
$lnm = $lg.Name
$lgused = $false
foreach ($db in $srv.Databases) {
$usr = $db.Users | where {$_.Login -eq $lnm}
$uid = [int]$usr.ID
if ($uid -gt 0) {
$lgused = $true
}
}
if ($lgused -eq $false) {
$n = new-object system.object
$n | Add-Member -type NoteProperty -name Name -value $lnm
$nodb += $n
}
}
Now that the $nodb collection is populated, use the Write-Output cmdlet to create T-SQL to drop the logins. Yes, I do that so I can look over the list and make sure that I want to drop each login. There may be extenuating circumstances that make me want to keep one or more of the qualifying logins, so I redirect the output of this last bit of code into a file, and after cleaning it up, I run it in a query window in Management Studio.
foreach ($n in $nodb) {
$nm = $n.Name
write-output "DROP LOGIN [$nm]"
write-output "GO"
}
Orphaned Database Users
That takes care of the logins, now clean up orphaned database users. You can iterate through the list of databases, and then through the Users collection in each database. For each User object in the collection (after testing to ensure it's not a system object), you can look through the Logins collection where the User object Login property matches the Login object Name property. If no such login exists, use the Write-Output cmdlet to generate a DROP USER statement to get rid of the database user. I added a little extra code to add a USE database statement each time you change databases, but only if we have a qualifying user to be dropped.
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
foreach ($db in $srv.Databases) {
$dbnm = $db.Name
$dbused = $False
foreach ($usr in $db.Users) {
$usrnm = $usr.Name
$ulog = $usr.Login
if ($usr.IsSystemObject -ne $True) {
$log = $srv.Logins | where {$_.Name -eq $ulog}
$lognm = $log.Name
if ($lognm -ne $ulog) {
if ($dbused -eq $False) {
$dbused = $True
write-output "USE [$dbnm]"
write-output "GO"
}
write-output "DROP USER [$usrnm]"
write-output "GO"
}
}
}
}
Again, this output is designed to be redirected to an output .sql file, and executed in a query window in SQL Server Management Studio (SSMS), after being reviewed.
Used with caution, this set of code will help you clean up orphaned logins and users in your servers.
