SQL - Fix Orphaned User
Octopus.Script exported 07/22/2020 by twerthi belongs to 'SQL Server' category.
Will fix an orphaned user in the database by re-associating the SID.
Parameters
When steps based on the template are included in a project's deployment process, the parameters below can be set.
SQL Server
autoFixSqlServer
The SQL Server to perform the work on
SQL Login
autoFixSqlLoginUserWhoHasRights
The login of the user who has permissions to create a database.
Leave blank for integrated security
SQL Password
autoFixSqlLoginPasswordWhoHasRights
The password of the user who has permissions to create SQL Logins
Leave blank for integrated security
Database Name
autoFixDatabaseName
The name of the database to create the user on
SQL Login
autoFixSqlLogin
The username to attach to the database if it does not exist
Script body
Steps based on this template will execute the following PowerShell script.
Show scriptfunction Get-ModuleInstalled
{
# Define parameters
param(
$PowerShellModuleName
)
# Check to see if the module is installed
if ($null -ne (Get-Module -ListAvailable -Name $PowerShellModuleName))
{
# It is installed
return $true
}
else
{
# Module not installed
return $false
}
}
function Get-NugetPackageProviderNotInstalled
{
# See if the nuget package provider has been installed
return ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))
}
function Install-PowerShellModule
{
# Define parameters
param(
$PowerShellModuleName,
$LocalModulesPath
)
# Set TLS order
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12
# Check to see if the package provider has been installed
if ((Get-NugetPackageProviderNotInstalled) -ne $false)
{
# Display that we need the nuget package provider
Write-Host "Nuget package provider not found, installing ..."
# Install Nuget package provider
Install-PackageProvider -Name Nuget -Force
}
# Save the module in the temporary location
Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force
}
# Define PowerShell Modules path
$LocalModules = (New-Item "$PSScriptRoot\Modules" -ItemType Directory -Force).FullName
$env:PSModulePath = "$LocalModules;$env:PSModulePath"
# Check to see if SqlServer module is installed
if (((Get-ModuleInstalled -PowerShellModuleName "SqlServer") -ne $true) -and ((Get-ModuleInstalled -PowerShellModuleName "SQLPS") -ne $true))
{
# Display message
Write-Output "PowerShell module SqlServer not present, downloading temporary copy ..."
# Download and install temporary copy
Install-PowerShellModule -PowerShellModuleName "SqlServer" -LocalModulesPath $LocalModules
# Display
Write-Output "Importing module SqlServer ..."
# Import the module
Import-Module -Name "SqlServer"
}
Write-Host "SqlLoginWhoHasRights $autoFixSqlLoginUserWhoHasRights"
Write-Host "SqlServer $autoFixSqlServer"
Write-Host "DatabaseName $autoFixDatabaseName"
Write-Host "SqlLogin $autoFixSqlLogin"
if ([string]::IsNullOrWhiteSpace($autoFixSqlLoginUserWhoHasRights) -eq $true){
Write-Host "No username found, using integrated security"
$connectionString = "Server=$autoFixSqlServer;Database=$autoFixDatabaseName;integrated security=true;"
}
else {
Write-Host "Username found, using SQL Authentication"
$connectionString = "Server=$autoFixSqlServer;Database=$autoFixDatabaseName;User ID=$autoFixSqlLoginUserWhoHasRights;Password=$autoFixSqlLoginPasswordWhoHasRights;"
}
# Build sql query
$sqlQuery = @"
DECLARE @OrphanedUsers TABLE
(
UserName VARCHAR(50) null,
UserSID VARBINARY(100) null
)
INSERT INTO @OrphanedUsers EXEC sp_change_users_login 'Report'
IF EXISTS ( SELECT UserName FROM @OrphanedUsers WHERE UserName = '$autoFixSqlLogin' )
BEGIN
PRINT '$autoFixSqlLogin is orphaned, fixing ...'
EXEC sp_change_users_login 'Auto_Fix', '$autoFixSqlLogin'
END
ELSE
PRINT '$autoFixSqlLogin is not orphaned.'
"@
# Execute the command to find orphaned users, then fix if matching
Invoke-SqlCmd -ConnectionString $connectionString -Query $sqlQuery -Verbose
To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.
Show JSON{
"Id": "e56e9b28-1cf2-4646-af70-93e31bcdb86b",
"Name": "SQL - Fix Orphaned User",
"Description": "Will fix an orphaned user in the database by re-associating the SID.",
"Version": 1,
"ExportedAt": "2020-07-22T00:41:40.086Z",
"ActionType": "Octopus.Script",
"Author": "twerthi",
"Packages": [],
"Parameters": [
{
"Id": "083897f2-d65d-45a5-b9fb-ef760a727303",
"Name": "autoFixSqlServer",
"Label": "SQL Server",
"HelpText": "The SQL Server to perform the work on",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "ead6a85f-71e6-4c42-b6b2-ef048edabcbd",
"Name": "autoFixSqlLoginUserWhoHasRights",
"Label": "SQL Login",
"HelpText": "The login of the user who has permissions to create a database.\n\nLeave blank for integrated security",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "eb648cdd-0390-4569-8af9-e4e51946585f",
"Name": "autoFixSqlLoginPasswordWhoHasRights",
"Label": "SQL Password",
"HelpText": "The password of the user who has permissions to create SQL Logins\n\nLeave blank for integrated security",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Sensitive"
}
},
{
"Id": "835159c0-7c5a-4714-ad9e-888dd29e6cd3",
"Name": "autoFixDatabaseName",
"Label": "Database Name",
"HelpText": "The name of the database to create the user on",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "7be27e8c-efef-4700-8b7f-fba78a25788f",
"Name": "autoFixSqlLogin",
"Label": "SQL Login",
"HelpText": "The username to attach to the database if it does not exist",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
}
],
"Properties": {
"Octopus.Action.Script.ScriptSource": "Inline",
"Octopus.Action.Script.Syntax": "PowerShell",
"Octopus.Action.Script.ScriptBody": "function Get-ModuleInstalled\n{\n # Define parameters\n param(\n $PowerShellModuleName\n )\n\n # Check to see if the module is installed\n if ($null -ne (Get-Module -ListAvailable -Name $PowerShellModuleName))\n {\n # It is installed\n return $true\n }\n else\n {\n # Module not installed\n return $false\n }\n}\n\nfunction Get-NugetPackageProviderNotInstalled\n{\n\t# See if the nuget package provider has been installed\n return ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))\n}\n\nfunction Install-PowerShellModule\n{\n # Define parameters\n param(\n $PowerShellModuleName,\n $LocalModulesPath\n )\n \n # Set TLS order\n [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12\n\n\t# Check to see if the package provider has been installed\n if ((Get-NugetPackageProviderNotInstalled) -ne $false)\n {\n \t# Display that we need the nuget package provider\n Write-Host \"Nuget package provider not found, installing ...\"\n \n # Install Nuget package provider\n Install-PackageProvider -Name Nuget -Force\n }\n\n\t# Save the module in the temporary location\n Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force\n\n}\n\n# Define PowerShell Modules path\n$LocalModules = (New-Item \"$PSScriptRoot\\Modules\" -ItemType Directory -Force).FullName\n$env:PSModulePath = \"$LocalModules;$env:PSModulePath\"\n\n# Check to see if SqlServer module is installed\nif (((Get-ModuleInstalled -PowerShellModuleName \"SqlServer\") -ne $true) -and ((Get-ModuleInstalled -PowerShellModuleName \"SQLPS\") -ne $true))\n{\n # Display message\n Write-Output \"PowerShell module SqlServer not present, downloading temporary copy ...\"\n\n # Download and install temporary copy\n Install-PowerShellModule -PowerShellModuleName \"SqlServer\" -LocalModulesPath $LocalModules\n \n # Display\n Write-Output \"Importing module SqlServer ...\"\n\n # Import the module\n Import-Module -Name \"SqlServer\" \n}\n\nWrite-Host \"SqlLoginWhoHasRights $autoFixSqlLoginUserWhoHasRights\"\nWrite-Host \"SqlServer $autoFixSqlServer\"\nWrite-Host \"DatabaseName $autoFixDatabaseName\"\nWrite-Host \"SqlLogin $autoFixSqlLogin\"\n\nif ([string]::IsNullOrWhiteSpace($autoFixSqlLoginUserWhoHasRights) -eq $true){\n\tWrite-Host \"No username found, using integrated security\"\n $connectionString = \"Server=$autoFixSqlServer;Database=$autoFixDatabaseName;integrated security=true;\"\n}\nelse {\n\tWrite-Host \"Username found, using SQL Authentication\"\n $connectionString = \"Server=$autoFixSqlServer;Database=$autoFixDatabaseName;User ID=$autoFixSqlLoginUserWhoHasRights;Password=$autoFixSqlLoginPasswordWhoHasRights;\"\n}\n\n# Build sql query\n$sqlQuery = @\"\nDECLARE @OrphanedUsers TABLE\n(\n\tUserName VARCHAR(50) null,\n\tUserSID VARBINARY(100) null\n)\n\nINSERT INTO @OrphanedUsers EXEC sp_change_users_login 'Report'\n\nIF EXISTS ( SELECT UserName FROM @OrphanedUsers WHERE UserName = '$autoFixSqlLogin' )\n\tBEGIN\n\t\tPRINT '$autoFixSqlLogin is orphaned, fixing ...'\n EXEC sp_change_users_login 'Auto_Fix', '$autoFixSqlLogin'\n END\nELSE\n\tPRINT '$autoFixSqlLogin is not orphaned.'\n\"@\n\n# Execute the command to find orphaned users, then fix if matching\nInvoke-SqlCmd -ConnectionString $connectionString -Query $sqlQuery -Verbose\n\n"
},
"Category": "SQL Server",
"HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/a381802920158308/step-templates/sql-fix-orphaned-user.json",
"Website": "/step-templates/e56e9b28-1cf2-4646-af70-93e31bcdb86b",
"Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
"$Meta": {
"Type": "ActionTemplate"
}
}Provided under the Apache License version 2.0.