This document outlines the minimum Windows-level permissions required for a DBA to remotely manage SQL Server without local administrator group membership. All permissions align with the Principle of Least Privilege.
Contents:
2. User Rights Assignments
3. File System (NTFS) Permissions
4. WMI Permissions
5. DCOM Permissions
6. Service Control Manager Permissions
7. SQL Server Configuration Manager Permissions
8. Summary Checklist
1. Windows Local Group Memberships
The DBA account requires membership in these built-in Windows groups:
| Group | Purpose |
|---|---|
| Remote Desktop Users | RDP connectivity to the server |
| Performance Monitor Users | Access to performance counters, Perfmon, Resource Monitor |
| Event Log Readers | Read access to Windows Event Logs (Application, System, Security) |
| Distributed COM Users | Remote WMI access and SSCM connectivity |
| Remote Management Users | WinRM / PowerShell remoting |
2. User Rights Assignments
Grant via Local Security Policy (secpol.msc) or Group Policy:
| User Right | Path | Purpose |
|---|---|---|
| Allow log on through Remote Desktop Services | Local Policies → User Rights Assignment | Authorizes RDP logon |
| Allow log on locally | Local Policies → User Rights Assignment | Required for some troubleshooting scenarios |
3. File System (NTFS) Permissions
The DBA account needs file system access to manage SQL Server files and perform maintenance.
Option A: Drive Root Permissions (Recommended)
Best for environments with dedicated SQL Server drives. Cleaner, easier to maintain.
| Drive | Permission |
|---|---|
| Data Drive (e.g., D:\) | Modify |
| Log Drive (e.g., L:\) | Modify |
| TempDB Drive (e.g., T:\) | Modify |
| Backup Drive (e.g., B:\) | Modify |
| System Drive (C:\) | Read (for SQL binaries, error logs, configuration) |
Option B: Explicit Folder Permissions
Best for shared drives, non-standard layouts, or environments requiring tightest security.
| Directory | Permission | Notes |
|---|---|---|
| \MSSQL\Backup | Modify | Manage backup files |
| \MSSQL\Data | Read | View database file locations |
| \MSSQL\Log | Read | Access SQL Server error logs (not transaction logs) |
| \MSSQL\Binn | Read | Access executables/configuration |
| \MSSQL\Install | Read | Installation scripts |
| Custom backup/data directories | Modify | As needed |
4. WMI Permissions
Required for remote monitoring, diagnostics, and SSCM. Configure via wmimgmt.msc:
| Setting | Value |
|---|---|
| Namespaces |
root\Microsoft\SqlServer root\Microsoft\SqlServer\ComputerManagement root\Microsoft\SqlServer\ComputerManagementXX |
| Permissions | Execute Methods, Enable Account, Remote Enable |
| Applies to | This namespace and subnamespaces |
Version numbers: ComputerManagement10 = SQL 2008, ComputerManagement11 = SQL 2012, ComputerManagement12 = SQL 2014, ComputerManagement13 = SQL 2016, ComputerManagement14 = SQL 2017, ComputerManagement15 = SQL 2019, ComputerManagement16 = SQL 2022
5. DCOM Permissions
Required for remote management tools and SSCM. Configure via dcomcnfg → COM Security tab:
| Permission Category | Required Permissions |
|---|---|
| Access Permissions → Edit Limits | Remote Access |
| Launch and Activation Permissions → Edit Limits | Remote Launch, Remote Activation |
6. Service Control Manager Permissions
Recommended: Group Policy Objects (GPO)
Allows you to define a group of users who can control services without other admin access.
| Step | Action |
|---|---|
| 1 | Create an AD Security Group (e.g., "SQLServerOperators") and add the DBA accounts |
| 2 | Create/edit a GPO linked to the OU containing SQL Server computer accounts |
| 3 | Navigate to: Computer Configuration → Policies → Windows Settings → Security Settings → System Services |
| 4 | Locate the SQL Server service, right-click → Properties. Check "Define this policy" and click Edit Security |
| 5 | Add the AD group and grant: Start, Stop, Pause, Continue, Read Configuration |
| 6 | Run gpupdate /force on target servers or wait for refresh |
Note: Applies to all relevant services: SQL Server (MSSQLSERVER), SQL Server Agent, SQL Server Browser, etc. For named instances, use SQL Server (INSTANCENAME).
Alternative: sc sdset
For quick or ad-hoc changes when GPO is not available. Requires understanding SDDL syntax.
REM View current permissions sc sdshow MSSQLSERVER REM Modify permissions (add user/group SID to ACL) sc sdset MSSQLSERVER "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)...(A;;RPWPCR;;;[YourUserSID])"
Alternative: subinacl (Legacy)
subinacl.exe is a legacy tool from the Windows Resource Kit. It still works but is no longer maintained. Use GPO or sc sdset for new implementations.
subinacl /service "MSSQLSERVER" /grant=DOMAIN\Username=TOP
7. SQL Server Configuration Manager (SSCM) Permissions
| Component | Required Permissions | Purpose |
|---|---|---|
| WMI Namespaces | Full Control on: root\Microsoft\SqlServer, root\Microsoft\SqlServer\ComputerManagement, and ComputerManagementXX | Read/write SQL Server configuration |
| Registry | Read access to: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server and HKLM\SOFTWARE\Microsoft\MSSQLServer | Instance enumeration and configuration |
| SQL Server Binaries | Read & Execute on: C:\Program Files\Microsoft SQL Server\ | Access to SSCM executable and libraries |
| Service Control | Permissions from Section 6 | Start/stop services, modify service accounts |
Note: Section 4 grants read-level WMI access (sufficient for monitoring). For full SSCM write capability (changing service accounts, startup parameters), Full Control on the WMI namespaces is required.
8. Summary Checklist
| Category | Requirement |
|---|---|
| Group Memberships | Remote Desktop Users, Performance Monitor Users, Event Log Readers, Distributed COM Users, Remote Management Users |
| User Rights | Allow log on through Remote Desktop Services, Allow log on locally |
| NTFS Permissions | Read/Modify on SQL Server directories as needed |
| WMI Permissions | Execute Methods, Enable Account, Remote Enable on SQL namespaces (Full Control for SSCM write access) |
| DCOM Permissions | Remote Launch, Remote Activation, Remote Access |
| Service Control | Grant via GPO, sc sdset, or subinacl |
| SSCM Access | WMI Full Control + Registry Read + SQL binary access + Service permissions |
References
Configure Windows Service Accounts and Permissions — Microsoft Learn
How to Start or Stop SQL Services Without OS Admin Rights — SQL Server Updates Blog
Security Changes in SQL Server 2008 — Microsoft Learn
No comments:
Post a Comment