INF: System Configuration Changes Seem to Not Take Effect

Last reviewed: May 2, 1997
Article 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.


Additional query words:
Keywords : kbtool kbusage SSrvEntMan
Version : 6.0
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.