Skip to main content

 none
How to change the MSSQLSERVER Service Startup Account from NT Service\MSSQLSERVER to custom Windows account ? RRS feed

  • Question

  • Hello


    Can you let me know an easy method to change the MSSQLSERVER Service Startup Account from NT Service\MSSQLSERVER to a custom Windows account in 2016 version ? Here are my requirements.


    1) Create a new Windows O.S account called sqlserver_owner.

    2) But do Not make it part of the administrators group.

    3) Create an SQL Server login principle with SYSADMIN fixed server role for this sqlserver_owner account. 

    I went to SQL Server configuration manager and chosen the SQL Server (MSSQLSERVER) account => right click and tried to change from NT Service\MSSQLSERVER to sqlserver_owner. It changed it. 

    4) Now when i login to the Windows server using sqlserver_owner, i am not able to stop and start this service. When i try from windows component services, the stop and start buttons are greyed out.  When i try to click on SSMS => right click => restart , it asks for Administrator's password.

    Basically, i would like to use a custom Windows login name as the MSSQLSERVER service owner, but not make it part of Windows administrators group.  Is it not possible in SQL server 2016 version ?

    For audit compliance databases, i do not want to make it part of Windows administrators group for segregation of duties. The DBA login should not be part of Windows administrators group

    I have seen this article https://msdn.microsoft.com/en-us/library/ms143504.aspx , but it has several steps and a series of permissions to be granted to sqlserver_owner to make it equal in nature to NT Service\MSSQLSERVER . But that is very lengthy and I hoping for a simple solution.

    In older versions such as 2008, all that i had to do was add to SQLServerMSSQLUser$ComputerName$InstanceName and SQLServerSQLAgentUser$ComputerName$InstanceName groups.

    -Srini


    Sunday, January 29, 2017 1:47 AM

All replies

  • Hi,

    You should change the SQL Server services accounts from SQL Server config tool, not directly from Control Panel.

    Sunday, January 29, 2017 1:48 AM
  • Only members of local administrators group have permission to stop/start/restart SQL services unless you follow the steps outlined in this guide to grant special privilege for non-administrators to do so:

    Stop, Start, Pause, Resume, Restart the Database Engine, SQL Agent Service, or SQL Browser Service

    Scroll down and click the link entitled: 'How to grant users rights to manage services in Windows Server 2003'

    Re: #4: Why would you want to login to a machine as sqlserver_owner (sql services startup account) and try to manage services as a user? Normally, you want to use a different user account from the account which is the service startup account, for security reasons. For example:

    • WINDOMAIN\sqlserver_owner (SQL Service startup account)
    • WINDOMAIN\dba_user (DBA login who administers the SQL instance, restarts services, etc)

    The second account is the one that you would typically want to be able to stop/start/restart SQL Services, not the service startup account itself. Both account can still be made member of Sysadmin fixed server role at the SQL instance level.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Sunday, January 29, 2017 2:49 AM
  • "method to change the MSSQLSERVER Service Startup Account from NT Service\MSSQLSERVER to a custom Windows account in 2016 version ?

    "

    Why?  That would make SQL Server less secure, not more.  And now you have to manage passwords.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Sunday, January 29, 2017 2:57 PM
  • He is asking "How?"

    He is not searching for advice on "why".


    "There's no fireplace like your fireplace"

    Tuesday, October 15, 2019 7:10 PM
  • My reason for why is so that I can create backups in network share locations directly. Is there a more secure way to enable that?
    Wednesday, October 30, 2019 5:17 AM