3

SQL Server runs under AD Account SQLAccount1. How to make it run under another account, SQLAccount2? How to do it un Powershell?

2 Answers 2

0
  1. The GUI way to do this is to go to SQL Server Configuration Manager. Go to the properties of the SQL Server and change the account name on the Log On tab. Click the Browse button.

enter image description here

Than enter the account that you want to SQL Server Log on as. Click Check Names, to make sure the account exists. Than click OK on select user or group and OK on SQL Server Properties.

enter image description here

Than restart.

2.This blog Hey, Scripting Guy! Blog, the author Aaron Nelson - SQLvariant shows how to do this in PowerShell.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "WIN7NetBook" #Suck in the server you want

#These just act as some queries about the SQL Services on the machine you specified.
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

#Same information just pivot the data
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List

#Specify the "Name" (from the query above) of the one service whose Service Account you want to change.

$ChangeService=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"} #Make sure this is what you want changed!

#Check which service you have loaded first

$ChangeService

$UName="DomainName\UserName"

$PWord="YourPassword"

$ChangeService.SetServiceAccount($UName, $PWord)

#Now take a look at it afterwards

$ChangeService
0
0

Microsoft has published a SQL Server module for Powershell Desired State Configuration (DSC) that you can use to change the SQL Server service account.

I have only tested this out with Powershell 5, so I don't know how well it works with Powershell Core, so you may experience

Please note this will RESTART YOUR SQL SERVER.

# THIS WILL RESTART YOUR SQL SERVER SERVICE
$credential = Get-Credential -Message 'Provide the username (domain\username) and password for the SQL Server service account to run under';


$SqlServiceAccountParams = @{
    ServerName     = 'localhost'
    InstanceName   = 'MSSQLSERVER'
    ServiceType    = 'DatabaseEngine'
    ServiceAccount = $credential
    RestartService = $true
};

# Required to be able to run the 
Install-Module 'SqlServerDsc';

$scriptBlock = {
    $ErrorActionPreference = 'stop'; 
    throw "I understand that this will RESTART THE SQL SERVER. Remove this line if you understand that your SQL Server will restart and are ready for the restart."; 
    Invoke-DscResource -Name 'SqlServiceAccount' -ModuleName 'SqlServerDsc' -Method Set -Property $SqlServiceAccountParams;   
}

invoke-command $scriptBlock

Powershell DSC - https://learn.microsoft.com/en-us/powershell/dsc/getting-started/wingettingstarted?view=dsc-1.1

SQL Server Powershell DSC Module - https://github.com/dsccommunity/SqlServerDsc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.