INF: System Configuration Changes Seem to Not Take Effect

• 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

name                  config_value

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

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

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.