SQL Server – How to Start Express Instance in Single User Mode

sql serversql-server-2012startuptroubleshooting

I want to start my SQL Server Express instance in single user mode to do some troubleshooting such as restoring the Master database.

I am unable to find the "Startup Parameter" options in SQL Server Configuration Manager.

I have found some blogs such as http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/, however I am unable to add the -m parameter to the 'Startup Parameters'.

See below for screenshots of my SQL Server Configuration Manager:

enter image description here

enter image description here

My working environment is:

  1. MS SQL Server 2012 Express 11.1.3000.0
  2. Windows Server 2012 R2
  3. SQL server 2012 Express is installed in a Domain
  4. OS is running under VMware

Is there any possible way to connect through the console? Any idea or suggestion would be appreciated.

I am able to see startup parameters from this T-SQL: 'select * from sys.dm_server_registry;', however I am unable to see the settings in SQL Server Configuration Manager.

Best Answer

According to the documentation, SQL Server Configuration Manager requires Local Administrator permissions since various SQL Server Configuration items are stored in the local system registry.

From the "SQL Server Properties (Startup Parameters Tab)" in the SQL Server Configuration Manager Help:

Use of this page is restricted to users who can change the related entries in the registry. This includes the following users.

  • Members of the local administrators group.

  • The domain account that is used by SQL Server, if the Database Engine is configured to run under a domain account.

You can confirm membership in the local Administrators via one of the following methods:

  • Start a command-prompt on the machine where SQL Server is installed, and run this:

    whoami /groups | find /i "BUILTIN\Administrators"
    
  • Assuming the machine is a member of your Active Directory Domain, you can run the "Active Directory Users and Computers" Microsoft Management Console, either from the Domain Controller for your domain, or from your workstation if you install the tool by downloading the Microsoft Remote Server Administration Tools. Once you have "Active Directory Users and Computers" running, you need to find your user account, then check the "Member of" tab to see if you are a "Domain Administrator" or "Enterprise Administrator".

  • On the server where SQL Server is installed, you can check the "User Accounts" from "Control Panel".