| Author | Klaas Vandenberghe (@PowerDbaKlaas) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Set-DbaPrivilege on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaPrivilege.
Grants essential Windows privileges to SQL Server service accounts for optimal performance and security.
Configures critical Windows privileges for SQL Server service accounts including Lock Pages in Memory (LPIM), Instant File Initialization (IFI), Logon as Batch, Logon as Service, and Generate Security Audits. These privileges are essential for SQL Server performance optimization and proper service operation, eliminating the need to manually configure them through Local Security Policy. The function automatically discovers SQL service accounts on target computers or allows you to specify custom accounts, then uses secedit to update the local security policy.
Requires Local Admin rights on destination computer(s).
Set-DbaPrivilege
[[-ComputerName] <DbaInstanceParameter[]>]
[[-Credential] <PSCredential>]
[-Type] <String[]>
[-EnableException]
[[-User] <String>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Set-DbaPrivilege -ComputerName sqlserver2014a -Type LPIM,IFI
Adds the SQL Service account(s) on computer sqlserver2014a to the local privileges 'SeManageVolumePrivilege' and 'SeLockMemoryPrivilege'.
PS C:\> 'sql1','sql2','sql3' | Set-DbaPrivilege -Type IFI
Adds the SQL Service account(s) on computers sql1, sql2 and sql3 to the local privilege 'SeManageVolumePrivilege'.
Specifies which Windows privileges to grant to the SQL Server service accounts. Accepts one or more values: 'IFI' (Instant File Initialization), 'LPIM' (Lock Pages in Memory), 'BatchLogon' (Log on as
a batch job), 'SecAudit' (Generate security audits), and 'ServiceLogon' (Log on as a service).
These privileges are essential for SQL Server performance and functionality - IFI speeds up database file operations, LPIM prevents memory paging for better performance, and the logon rights ensure
services can start properly.
Multiple privileges can be specified together for comprehensive SQL Server optimization.
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value | |
| Accepted Values | IFI,LPIM,BatchLogon,SecAudit,ServiceLogon |
The target SQL Server instance or instances.
| Alias | cn,host,Server |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value | $env:COMPUTERNAME |
Credential object used to connect to the computer as a different user.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Specifies a custom user account to receive the privileges instead of automatically discovering SQL Server service accounts.
Use this when you need to grant privileges to a specific account that will run SQL Server services, or when the automatic service account detection doesn't work in your environment.
Accepts domain accounts (DOMAIN\User) or local accounts - ensure the account exists and will be used by SQL Server services.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |