While testing a scenario for my client, I ran into a behavior which I was not aware of. Let us learn about the error which is related to Empty Startup Parameters in SQL Server Configuration Manager.
I logged in to the virtual machine (my lab environment) and wanted to change ERRORLOG location. You can refer my below blog to find various ways to know location of ERRORLOG file.
SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
I used configuration manager and I was surprised to see that there was no parameter listed.
When I checked other system, there is no way to remove those parameters from the interface. I searched further and found that they are stored in the registry key. I opened below key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Parameters
As soon as I clicked on parameter key, I was welcomed by below message.
It was clear now that due to permission issue on registry key, configuration manager is showing empty box. To confirm this, I checked another working machine and found below permission.
As we can see, the permission is restricted to Administrators group on the machine, SYSTEM account and Service SID.
To view startup parameter, we should log into the machine with an account which has Administrator privileges on the machine. In the above test, I was logged in with an account which was a normal user on the server. When I logged off and logged in with the Administrator account, I was able to see the startup parameters as expected.
Do you have such restrictions in your production environment?
Reference: Pinal Dave (https://blog.sqlauthority.com)
Can you please help me how to enable start up parameters in sql server 2008 R2 version