How to Start or Stop SQL Services without OS Admin Rights
Scenario
You wish to grant users who do NOT have OS administrative rights the ability to start/stop SQL Server & SQL Agent services.
Please review the following related blog post: https://blogs.msdn.com/b/sqlupdates/archive/2015/05/14/how-to-start-or-stop-sql-services-without-uac-prompts.aspx
(Note: to see the pictures below at a better resolution, please click on the picture and this will open a link to the source image.)
Why Would You Ever Need to Do This?
<Updated 5/18/2015 - Added additional clarification of use case for this approach>
This is not a common scenario, the use case is meant for an environment where there are different levels of administrators. So say you have a group of administrators which can only administer the SQL Services but you do not wish, or are not allowed, to grant them entire OS level administrative privileges. In this case it makes sense to grant more granular permissions. Reference: https://download.microsoft.com/download/D/2/D/D2D931E9-B6B5-4E3B-B0AF-22C749F9BB7E/SQL_Server_Separation_of_Duties_White_Paper_Jul2011.docx
In my experience, the few environments I've seen which have implemented this approach do it because (1) they have to abide by strict security compliance mandates or regulations to ensure only required permissions are granted -or- (2) they are adding on additional administrators from other departments to a centralized or consolidated server and want to grant those admins permissions solely to the resources which they are allowed to manage on that consolidated server -or- (3) they have a central admin/support group which is allowed to troubleshoot/maintain departmental servers but full OS admin rights on the departmental servers is not permitted to the remote admins.
Ultimately, the choice to implement this should decided by business need. You should not do something simply because it can be done. There are certainly a few scenarios where this is a valid approach and this is why we expose group policies for this specific granular control of permissions when it is required.
Solution
You can grant a non-administrator the right to start/stop a specific service via the following methods:
1. Local Security Templates snap-in/Security Configuration and Analysis snap-in
a. Server Security Policy Management in Windows Server 2008 https://technet.microsoft.com/en-us/library/cc754373(v=WS.10).aspx
b. You can extend this to a GPO, and it is easier to maintain because users can start/stop services the way they normally do. You would not need to maintain any special scripts or shortcuts.
| WALKTHROUGH: Add Local Security Template to Grant Non-OSAdmin Permissions to Start/Stop SQL Service |
I. Import the two security snap-ins: Start->run->mmc.exe->File->"Add/Remove Snap-in" a. Security Configuration and Analysis b. Security Templates
II. Create a Security Template a. Right click on "Security Templates (tree item)" b. Left click on "New Template Search Path...
c. Browse for a folder (I chose desktop in this example) d. Now right click on the template path, and choose "New Template", enter a name for your template
e. Within the newly created template, navigate to "System Services" (tree item) f. Search for the SQL Service you wish to set permissions for, in my case "SQL Server (SQL14INST)", right click on properties
g. Click on "Define this policy setting in the template (check box)" in "SQL Server (SQL14INST) Properties" h. Select the "Edit Security" button and then the "Add" button to add the user group you wish to grant start/stop permissions to i. Ensure the "Allow Start, stop and pause" check box is checked
j. Save the template
III. Import the previously created security template to analyze and configure the template settings on the server: a. Click on "Security Configuration and Analysis (tree item)", right click and choose "Open Database"
b. Navigate to the directory where you wish to create your new security db, specify a name for your db, seect the OPEN button:
c. Now you will be prompted for a template, navigate to the previously created template, click OPEN
d. Now, right click on "Security Configuration and Analysis (tree item)" , select "Analyze Computer Now... (menu item)"
e. Navigate to "System Services (tree item)" , note that the SQL Service previously configured is showing a marking indicating the permissions on the machine do not currently match the permissions set in the template
f. Note: You can right click on the SQL service->properties-> choose View Security to take a snapshot of the existing security settings before modifying them
g. To apply the permissions defined in the template, you must configure them. h. Right click on "Security Configuration and Analysis (tree item)" -> click on "Configure Computer Now... (menu item)"
i. You then repeat step d) above to re-analyze the template settings against your machine, you should now see a green OK, indicating the permissions on the server now match the configured template permissions
IV. Now any user who is part of the group you configured above is able to stop/start the SQL service in question. It is not required for the user to be part of the administrators group. Moreover, the user is not required to open an elevated command prompt to modify the service state. a. Note: A user who is part of the administrators group, but NOT part of this group which is configured with explicit permissions to start/stop the service, would need an elevated prompt to modify the service state. By adding the admin user to this configured group, you can avoid the need to have him/her get prompted for elevation. b. Example: User who is NOT part of the configured group(even if user is OS Admin), using a non-Elevated command prompt:
c. Example: User who IS part of the configured group (no need for elevated command prompt):
|
2. [Preferred Method] Define a group policy. You can extend the local security templates from option (1) above to the domain via a group policy
a. It is possible to import security templates created with the Security Templates snap-in into a GPO.
b. Security Policy Settings Technical Overview https://technet.microsoft.com/en-us/library/jj966251.aspx
Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services