INF: System Configuration Changes Seem to Not Take Effect

ID: Q151595

The information in this article applies to:

Microsoft SQL Server version 6.0

SUMMARY

When using the SQL Enterprise Manager (SEM), to change system level configuration options (audit logins, security mode, autostart, etc.), the changes may not or do not seem to take effect.

MORE INFORMATION

There are two reasons you may not see these changes take effect. First, you may need to restart the SQL Server in order for the changes to take effect. Changes like audit logins are not dynamic options and only take effect when the SQL Server service is restarted. Refer to the SQL Server "Books Online" for more details.

Second, it may appear your changes revert back to the original configuration settings. This is generally caused by a registry permission issue. By default, SEM uses the default settings to display. This appears to revert your settings.

The problem may also manifest itself in the SQL Security Manager. When you attempt to launch the Security Manager you will receive error, "Unable to query SQL Server Security Information, security stored procedures have not been installed properly."

You can use the following commands to help identify the registry permission problem:

exec master.dbo.xp_loginconfig

Expected results are:


name                  config_value
--------------------- ---------------------
login mode            standard
default login         guest
default domain        DOMAIN
audit level           none
set hostname          false
map _                 domain separator
map $                 (null)
map #                 -

You can also try something like the following. Make sure to check the original value of the registry key before performing the command.


   exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
       'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
       'LoginMode', 'REG_DWORD', 0

This sets the Login mode to Standard.
Results should be (0 row(s) affected).

If the registry permissions are not correctly set, you will get errors like "RegCreateKey access denied" or no rows returned from the xp_loginconfig extended stored procedure.

To correct the problem, check the Control Panel\Services application, MSSQLServer registry key, Startup button to see how SQL Server is logging on. Then use the Registry Editor (REGEDT32.EXT) to make sure this user or a group that the user belongs to has full control of the MSSQLServer registry keys. Be sure to check the recursive button when applying the new permissions to the MSSQLServer tree.

Keywords : kbtool kbusage SSrvEntMan
Version : 6.0
Platform : WINDOWS
Issue type :