functions/Repair-SqlOrphanUser.ps1
|
Function Repair-SqlOrphanUser { <# .SYNOPSIS Find orphan users with existing login and map .DESCRIPTION An orphan user is defined by a user that does not have their matching login. (Login property = "") If the matching login exists it must be: .Enabled .Not a system object .Not locked .Have the same name that login You can drop users that does not have their matching login by especifing the parameter -RemoveNotExisting This will be made by calling Remove-SqlOrphanUser function .PARAMETER SqlServer The SQL Server instance. .PARAMETER SqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, then pass $scred object to the -SqlCredential parameter. Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user. .PARAMETER RemoveNotExisting If passed, all users that not have their matching login will be dropped from database .NOTES Original Author: Cláudio Silva (@ClaudioESSilva) dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. .LINK https://dbatools.io/Repair-SqlOrphanUser .EXAMPLE Repair-SqlOrphanUser -SqlServer sql2005 Will find all orphan users of all databases present on server 'sql2005' .EXAMPLE Repair-SqlOrphanUser -SqlServer sqlserver2014a -SqlCredential $cred Will find all orphan users of all databases present on server 'sqlserver2014a'. Will be verified using SQL credentials. .EXAMPLE Repair-SqlOrphanUser -SqlServer sqlserver2014a -Databases db1, db2 Will find all orphan users on both db1 and db2 databases .EXAMPLE Repair-SqlOrphanUser -SqlServer sqlserver2014a -RemoveNotExisting Will find all orphan users of all databases present on server 'sqlserver2014a' Will also remove all users that does not have their matching login #> [CmdletBinding(SupportsShouldProcess = $true)] Param ( [parameter(Mandatory = $true, ValueFromPipeline = $true)] [Alias("ServerInstance", "SqlInstance")] [object[]]$SqlServer, [object]$SqlCredential, [switch]$RemoveNotExisting ) DynamicParam { if ($SqlServer) { return Get-ParamSqlDatabases -SqlServer $SqlServer -SqlCredential $SqlCredential } } BEGIN { Write-Output "Attempting to connect to Sql Server.." $sourceserver = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential } PROCESS { # Convert from RuntimeDefinedParameter object to regular array $databases = $psboundparameters.Databases if ($databases.Count -eq 0) { $databases = $sourceserver.Databases | Where-Object {$_.IsSystemObject -eq $false -and $_.IsAccessible -eq $true} } else { if ($pipedatabase.Length -gt 0) { $Source = $pipedatabase[0].parent.name $databases = $pipedatabase.name } else { $databases = $sourceserver.Databases | Where-Object {$_.IsSystemObject -eq $false -and $_.IsAccessible -eq $true -and ($databases -contains $_.Name)} } } if ($databases.Count -gt 0) { foreach ($db in $databases) { try { #if SQL 2012 or higher only validate databases with ContainmentType = NONE if ($sourceserver.versionMajor -gt 10) { if ($db.ContainmentType -ne [Microsoft.SqlServer.Management.Smo.ContainmentType]::None) { Write-Warning "Database '$db' is a contained database. Contained databases can't have orphaned users. Skipping validation." Continue } } Write-Output "Validating users on database '$db'" $Users = $db.Users | Where {$_.Login -eq "" -and ("dbo","guest","sys","INFORMATION_SCHEMA" -notcontains $_.Name)} if ($Users.Count -gt 0) { Write-Output "Orphan users found on database '$db'" foreach ($User in $Users) { $ExistLogin = $sourceserver.logins | Where-Object {$_.Isdisabled -eq $False -and $_.IsSystemObject -eq $False -and $_.IsLocked -eq $False -and $_.Name -eq $User.Name } if ($ExistLogin) { $query = "ALTER USER " + $User + " WITH LOGIN = " + $User $sourceserver.Databases[$db.Name].ExecuteNonQuery($query) | Out-Null Write-Output "User '$($User.Name)' mapped with their login" } else { if ($RemoveNotExisting -eq $true) { #Will call Remove-SqlOrphanUser function } else { Write-Output "Orphan user $($User.Name) does not have matching login" } } } } else { Write-Output "No orphan users found on database '$db'" } } catch { throw $_ } } } else { Write-Output "There are no databases to analyse." } } END { $sourceserver.ConnectionContext.Disconnect() } } |