In SQL Server, a login is an instance level object that is used for authentication. It is mapped to a database user, which controls permissions at the database level. These objects (login and user) are tied to one another via a SID. If the login is deleted and then recreated, or if you restore your production database to a test environment, those SID’s will no longer match and orphaned users will result.
Information on how to resolve this situation can be found on MSDN. However, if you need to fix more than one user this can be painful. It also does not work for the “dbo” user, requiring you to take additional steps in order to repair both. In the interest of handling refreshes to our development and test instances in a more efficient way I’ve created the script below. It takes two parameters, one for the source instance and another for the destination. It will cycle through the databases on the destination instance and query the source for login information pertaining to any orphaned users. Some informational messages have been built in to help you identify issues, such as a login not existing on your destination instance.
There are a couple of disclaimers. This script assumes that your logins will have the same name on both instances. If your production instance has an account named “sqlsvc” and the test instance equivalent is named “sqlsvc_test”, then it will not sync the user to that login. For the situation I’m working with there is no reliable standard in account names for me to rely on. If your environment is more standardized then please feel free to build in that additional logic.
I hope this will be of help to those out there fatigued by running sp_change_users_login one user at a time. You call the script as “./FixOrphans.ps1 -SourceSQLInstance YourSourceName -DestSQLInstance -YourDestinationName”. If you don’t provide the parameters up front it will prompt you for them.
As always, if you have any questions please let me know.
#Parameters passed in for source and destination instances.
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$SourceSQLInstance,
[Parameter(Mandatory=$True)]
[string]$DestSQLInstance
)
#Save current working directory.
Push-Location
#Import only the Cmdlet we need from the SQL Module without warning about name checking.
Import-Module -DisableNameChecking -Name SQLPS -Cmdlet Invoke-Sqlcmd
#Return to working directory.
Pop-Location
#Prompt for source and destination information. (Alternative to the parameter method at the top.)
#$SourceSQLInstance = Read-Host -Prompt 'Source Instance'
#$DestSQLInstance = Read-Host -Prompt 'Destination Instance'
#Get list of available logins on the destination instance.
$LoginList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from master..syslogins"
#Get list of databases on the destination instance.
$DatabaseList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from sys.databases"
#Process each database that was pulled.
foreach ($Database in $DatabaseList)
{
#Make the database name a string for easy use.
$DatabaseName = $Database.name
#Get list of orphaned users.
$OrphanList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "exec sp_change_users_login 'Report'"
#Check the orphan count, proceed if there are any.
if ($OrphanList -ne $null -or $OrphanList.Count -gt 0)
{
#Process each orphaned user.
foreach ($Orphan in $OrphanList)
{
#Set database user pricipal name.
$User = $Orphan.UserName
#Set login name from source instance.
$Login = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $DatabaseName -query "SELECT S.loginname FROM $DatabaseName.sys.database_principals as P
join master..syslogins S on P.sid = S.sid where P.name = '$User'"
#Make the login name a string for easy use.
$LoginName = $Login.loginname
#Test for whether the login exists on this instance. If it does, match it to the user.
if ($LoginList.name -contains $LoginName)
{
#Handle orphand dbo users. These cannot be updated by sp_change_users_login.
if ($User -eq "dbo")
{
$Message = "Changing owner to $LoginName for database $DatabaseName."
$Message
Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "EXEC sp_changedbowner '$LoginName';"
}
#For all other cases outside of dbo, use sp_change_users_login.
else
{
$Message = "Matching user $User to login $LoginName for database $DatabaseName on $DestSQLInstance."
$Message
#Update the user's login.
Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "exec sp_change_users_login 'update_one', '$User', '$LoginName'"
}
}
#Give an informative message when the login is not in the list.
else
{
$Message = "Failed to update user $User for database $DatabaseName because there is no login on $DestSQLInstance."
Write-Host $Message -ForegroundColor Yellow
}
}
}
#Inform if there are no orphans for this database. Happy day!
else
{
$Message = "There are no orphans in database $DatabaseName."
$Message
}
}