PowerShell Advanced Function for “When You Are Completely Locked Out”

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null  function Force-BuiltinAdministratorsIntoSysadmin {  <# .synopsis Script to be used under user context within BUILTIN\Administrators when locked out of Sql Server, to grant sysadmin role to BUILTIN\Administrators.  .description copyright = CC-BY-NC-SA author(s) = Robert Matthew Cook, date(s) = 2010-07-27,  Attempts to stop Sql Server service on local machine, then restart in single user mode.  Adds and/or creates the BUILTIN\Administrators group Login to sysadmin role.  Finally restarts Sql Server service in normal operation.   .example Force-BuiltinAdministratorsIntoSysadmin "." .example Force-BuiltinAdministratorsIntoSysadmin "MyVirtualSqlName" 30 .example $DebugPreference="Inquire";Force-BuiltinAdministratorsIntoSysadmin "VirtualSqlName\MyNamedInstance" 60;  .LINK http://msdn.microsoft.com/en-us/library/dd207004.aspx http://msdn.microsoft.com/en-us/library/ms188236.aspx http://msdn.microsoft.com/en-us/library/ms178061.aspx http://www.sqlservercentral.com/articles/Administration/68271/ http://www.sqlmashup.com #>  [CmdletBinding(SupportsShouldProcess=$false,SupportsTransactions=$false,ConfirmImpact="None")]  Param ( [Parameter(Mandatory=$true, Position=0, HelpMessage="Enter name of Sql Server Instance.")] [ValidateNotNullOrEmpty()] [String] $ServerInstance , [Parameter(Mandatory=$false, Position=1, HelpMessage="Enter number of seconds to wait for services to change state. Higher values recommended for clusters.")] [ValidateRange(0,300)] [Int] $StateChangeWait=10 )  #test windows permissions if(-not([Security.Principal.WindowsPrincipal]([System.Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)) { Throw "ERROR >| Script must be ran under BUILTIN\Administrators credentials! |< ERROR" Return 4 }  #determine script variables $DebugResult = "" Write-Debug ("SqlInstanceName >| $ServerInstance |< SqlInstanceName") if ($ServerInstance.Contains("\")) { $ServiceInstance = $ServerInstance.Substring(($ServerInstance.IndexOf("\") + 1), ($ServerInstance.Length - $ServerInstance.IndexOf("\") - 1)) } else { $ServiceInstance = "MSSQLSERVER" } Write-Debug ("ServiceInstance >| $ServiceInstance |< ServiceInstance") $SqlDisplayName = "SQL Server (" + $ServiceInstance + ")" Write-Debug ("SqlDisplayName >| $SqlDisplayName |< SqlDisplayName") $SingleUserModeCommand = 'net start "' + $SqlDisplayName + '" /m' Write-Debug ("SingleUserModeCommand >| $SingleUserModeCommand |< SingleUserModeCommand") $AgentDisplayName = (Get-Service -Name "SQL Server Agent ($ServiceInstance)" -ErrorAction "SilentlyContinue").DisplayName Write-Debug ("AgentDisplayName >| $AgentDisplayName |< AgentDisplayName") $ServerName = $ServerInstance.Replace($ServiceInstance, "").Replace("\", "") Write-Debug ("ServerName >| $ServerName |< ServerName") $SqlResource = Get-WmiObject -class "MSCluster_Resource" -namespace "root\MSCluster" -ErrorAction "SilentlyContinue" | Where-Object {$_.Type -eq "SQL Server"} | Where-Object {(($_.PrivateProperties.VirtualServerName -eq $ServerName) -and ($_.PrivateProperties.InstanceName -eq $ServiceInstance))} $AgentResource = Get-WmiObject -class "MSCluster_Resource" -namespace "root\MSCluster" -ErrorAction "SilentlyContinue" | Where-Object {$_.Type -eq "SQL Server Agent"} | Where-Object {(($_.PrivateProperties.VirtualServerName -eq $ServerName) -and ($_.PrivateProperties.InstanceName -eq $ServiceInstance))} $SqlGroup = Get-WmiObject -Query ("ASSOCIATORS OF {MSCluster_Resource.Name='" + $SqlResource.Name + "'} Where ResultClass = MSCluster_ResourceGroup") -namespace "root\MSCluster" -ErrorAction "SilentlyContinue" $SqlNode = Get-WmiObject -Query ("ASSOCIATORS OF {MSCluster_ResourceGroup.Name='" + $SqlGroup.Name + "'} Where ResultClass = MSCluster_Node") -namespace "root\MSCluster" -ErrorAction "SilentlyContinue" #preparing sql cluster if ($SqlResource -ne $null) { Write-Debug ("Cluster Sql Resource Name >| " + $SqlResource.Name + " |< Cluster Sql Resource Name") Write-Debug ("Cluster Sql Group Name >| " + $SqlGroup.Name + " |< Cluster Sql Group Name") Write-Debug ("Cluster Sql Node Name >| " + $SqlNode.Name + " |< Cluster Sql Node Name") #ensure sql dependancies on local node if ($SqlNode.Name -ne $Env:ComputerName) { Write-Host "Moving Sql Group..." $SqlGroup.MoveToNewNode($Env:ComputerName, $StateChangeWait) $SqlNode = Get-WmiObject -Query ("ASSOCIATORS OF {MSCluster_ResourceGroup.Name='" + $SqlGroup.Name + "'} Where ResultClass = MSCluster_Node") -namespace "root\MSCluster" if ($SqlNode.Name -ne $Env:ComputerName) { Throw "ERROR >| Sql Resource Group not moved to local node! |< ERROR" Return 3 } } #check not online ClusterResourceOffline[=3] ^not all states handled^ CLUSTER_RESOURCE_STATE Enumeration <http://msdn.microsoft.com/en-us/library/bb309168(v=VS.85).aspx> if ($SqlResource.State -ne 3) { #take cluster resource sql offline Write-Host "Stopping Sql Resource..." $SqlResource.TakeOffline($StateChangeWait) $SqlResource = Get-WmiObject -class "MSCluster_Resource" -namespace "root\MSCluster" -ErrorAction "SilentlyContinue" | Where-Object {$_.Type -eq "SQL Server"} | Where-Object {(($_.PrivateProperties.VirtualServerName -eq $ServerName) -and ($_.PrivateProperties.InstanceName -eq $ServiceInstance))} $DebugResult = $SqlResource.State Write-Debug ("Cluster Resource Sql State {3} >| $DebugResult |< Cluster Resource Sql State {3}") } } #preparing sql standalone else { #check for stopped agent ^not all states handled^ if ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $AgentDisplayName}).State -ne "Stopped") { #stop agent if ($AgentDisplayName -ne $null) { Write-Host "Stopping Agent Service..." $DebugResult = ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $AgentDisplayName}).StopService()).ReturnValue Write-Debug ("Agent Stop Service Result {0} >| $DebugResult |< Agent Stop Service Result {0}")  #wait then check new state Start-Sleep -seconds $StateChangeWait $DebugResult = (Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $AgentDisplayName}).State Write-Debug ("Agent State {Stopped} >| $DebugResult |< Agent State {Stopped}") }     }  #check for stopped sql ^not all states handled^ if ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $SqlDisplayName}).State -ne "Stopped") { #stop sql Write-Host "Stopping Sql Service..." $DebugResult = ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $SqlDisplayName}).StopService()).ReturnValue Write-Debug ("Sql Stop Service Result {0} >| $DebugResult |< Sql Stop Service Result {0} ")  #wait then check new state Start-Sleep -seconds $StateChangeWait $DebugResult = (Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $SqlDisplayName}).State Write-Debug ("Sql State {Stopped} >| $DebugResult |< Sql State {Stopped}") } }  Write-Host "Starting Sql Server [single user mode]..." Invoke-Expression $SingleUserModeCommand  #build sql to check preconditions $SqlCall = "sqlcmd -S$ServerInstance" $SqlCall += ' -E -Q "SET NOCOUNT ON;' $SqlCall += "SELECT" $SqlCall += "  'IS_SRVROLEMEMBER(sysadmin, BUILTIN\Administrators)=' + CONVERT(char(1), IS_SRVROLEMEMBER('sysadmin', 'BUILTIN\Administrators'))" $SqlCall += " ,'IS_SRVROLEMEMBER(sysadmin)=' + CONVERT(char(1), IS_SRVROLEMEMBER('sysadmin'))" $SqlCall += " ,'SYSTEM_USER=' + CONVERT(varchar(20), SYSTEM_USER)" $SqlCall += " AS [DEBUG];" $SqlCall += '" -h-1' Write-Debug ("Preconditions Call >| $SqlCall |< Preconditions Call")  #check preconditions $DebugResult = Invoke-Expression -command $SqlCall Write-Debug ("Preconditions Result >| $DebugResult |< Preconditions Result")  #build sql to test sql permissions $SqlCall = "sqlcmd -S$ServerInstance" + ' -E -Q "SET NOCOUNT ON;' + "SELECT CONVERT(bit, IS_SRVROLEMEMBER('sysadmin')) AS [Is_Sysadmin];" + '" -h-1' Write-Debug ("Sql Permissions Test >| $SqlCall |< Sql Permissions Test")  $DebugResult = Invoke-Expression -command $SqlCall Write-Debug ("Sql Permissions Result {1}>| $DebugResult |< Sql Permissions Result {1}") #test sql permissions if (-not([bool]$DebugResult)) { Throw "ERROR >| TSQL must be ran under sysadmin credentials! <| ERROR" Return 2 }  #build sql to make security changes $SqlCall = "sqlcmd -S$ServerInstance" $SqlCall += ' -E -Q "SET NOCOUNT ON;' $SqlCall += "DECLARE @GRANT_SYSADMIN int;" $SqlCall += "IF NOT EXISTS (SELECT 0 FROM sys.server_principals WHERE UPPER(name) = UPPER(N'BUILTIN\Administrators')) CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];" $SqlCall += "EXEC @GRANT_SYSADMIN = master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin';" $SqlCall += "SELECT CONVERT(char(1), CONVERT(bit, @GRANT_SYSADMIN)) AS [RESULT];" $SqlCall += '" -h-1'  #make security changes Write-Debug ("Sql Call >| $SqlCall |< Sql Call") $DebugResult = Invoke-Expression -command $SqlCall  #confirm success if (($? -ne $true) -or ($DebugResult -ne 0)) { Write-Debug ("Security Change Result >| $DebugResult |> Security Change Result") Throw "ERROR >| BUILTIN\Administrators could not be updated! |< ERROR" Return 1 } Write-Debug ("Security Change Result >| $DebugResult |> Security Change Result")  #build sql to shutdown $SqlCall = "sqlcmd -S$ServerInstance" $SqlCall += ' -E -Q "SET NOCOUNT ON;' $SqlCall += "SHUTDOWN;" $SqlCall += '" -h-1'  #perform shutdown Write-Host "Stopping Sql Server [single user mode]..." Write-Debug ("Sql Call >| $SqlCall |< Sql Call") $DebugResult = Invoke-Expression -command $SqlCall #confirm success Write-Debug ("Shutdown Result >| $DebugResult |> Shutdown Result")  #start normal operation cluster if ($AgentResource -ne $null) {    #bring cluster resource online if ($AgentDisplayName -ne $null) { Write-Host "Starting Agent Resource..." $AgentResource.BringOnline($StateChangeWait) $DebugResult = $AgentResource.State Write-Debug ("Cluster Resource Agent State {2} >| $DebugResult |< Cluster Resource Agent State {2}") } else { Write-Host "Starting Sql Resource..." $SqlResource.BringOnline($StateChangeWait) $DebugResult = $SqlResource.State Write-Debug ("Cluster Resource Sql State {2} >| $DebugResult |< Cluster Resource Sql State {2}") } } #start normal operation non-cluster else { #start sql service Write-Host "Starting Sql Service..." $DebugResult = ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $SqlDisplayName}).StartService()).ReturnValue Write-Debug ("Sql Start Service Result {0} >| $DebugResult |< Sql Start Service Result {0}")  #wait then check new state Start-Sleep -seconds $StateChangeWait $DebugResult = (Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $SqlDisplayName}).State Write-Debug ("Sql State {Started} >| $DebugResult |< Sql State {Started}")  #start agent service if ($AgentDisplayName -ne $null) { Write-Host "Starting Agent Service..." $DebugResult = ((Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $AgentDisplayName}).StartService()).ReturnValue Write-Debug ("Agent Start Service Result {0} >| $DebugResult |< Agent Start Service Result {0}")  #wait then check new state Start-Sleep -seconds $StateChangeWait $DebugResult = (Get-WmiObject -class "Win32_Service" | Where-Object {$_.DisplayName -eq $AgentDisplayName}).State Write-Debug ("Agent State {Started} >| $DebugResult |< Agent State {Started}") } }  Return 0 }

[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null function Force-BuiltinAdministratorsIntoSysadmin { [CmdletBinding(SupportsShouldProcess=$false,SupportsTransactions=$false,ConfirmImpact=”None”)] Param ( [Parameter(Mandatory=$true, Position=0, HelpMessage=”Ent … http://sqlmashup.posterous.com/powershell-advanced-function-for-when-you-are

Advertisements

About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s