Currently we have 3 AD users that can log into a Windows Server:

  • MRT\administrator
  • MRT\adminJohn
  • MRT\adminAlice

They all logged into the Windows Server 2012 and then does the following:

  • MRT\administrator created a SQL instance: MSSQLSERVER
  • MRT\adminJohn created a SQL instance: MYPROD_INSTANCE
  • MRT\adminAlice created a SQL instance: MYPROD_INSTANCE2

The problem is adminJohn can restart/stop/start adminAlice's SQL instance and vice versa.

How can I restrict them from restarting/stopping/starting other instances (SQL Server, SQL Agent and other services) from both the SSMS and SQL Configuration Manager?

I tried adding a LogOn account to the service but they can still restart, stop, start each other services.

  • 6
    this has more to do with windows services privileges than with database services, – McNets Nov 16 '16 at 19:54
  • 1
    Do the users all need to be local box admin? Is it a need, want or "we've always had it so you can pry it from my cold dead fingers"? Can't block admins under most circumstances in the Windows or even most common OS security models. If you're admin/root, you're kinda god of the box unless it's one of those custom configured secure workstations. If you can't take admin away from them, VMs are the way to go. – SQLmojoe Nov 17 '16 at 0:12
  • I can't create more virtual machines mainly because they already bought the VM, Disk space and separated the instances so i dont have a choice to go this route of windows privileges. – Raidenlee Nov 17 '16 at 0:58

This article can run you through the basics of getting this configured to your liking. Be warned, you may need to enlist the help of resources within your organization with elevated rights on the domain to implement it: http://social.technet.microsoft.com/wiki/contents/articles/5752.how-to-grant-users-rights-to-manage-services-start-stop-etc.aspx

  • Thanks, this looks promising! Have you ever done something like this before? – Raidenlee Nov 17 '16 at 0:52
  • No, because I would always push to avoid running all of these services on the same machine. My suggestion would be to place them all on their own individual VMs so you can split up the workloads and access controls. It's not viable in a physical hardware-based environment, but if you have VMs, this approach defines much more formal boundaries between the backends. Just because you setup a user to restart a specific service doesn't mean they can't elevate permissions and run amok (e.g. especially if their creds are compromised) regardless the best attempts to lock them down. – John Eisbrener Nov 18 '16 at 17:32
  • Makes sense to do it this way but the company already bought the single VM and can't afford another. Wish i could have advised them before doing such thing. Thanks much. – Raidenlee Nov 18 '16 at 17:57

My personal opinion is giving each

MRT\administrator , MRT\adminJohn , MRT\adminAlice

a separate virtual machine. This will reduce your cost of needing to develop advanced security settings.

In addition, while SQL Server can have multiple instances, SSIS cannot. If you have multiple SQL Server instances you will have to configure them to work with one SSIS instance.

  • Hey thanks for this insight. Didn't know all instances would be tied to one instance of SSIS. I can't create more virtual machines mainly because they already bought the VM, Disk space and separated the instances so i dont have a choice to go this route. – Raidenlee Nov 17 '16 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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