INF: Troubleshooting SQL Security Manager Messages

 Last reviewed: April 10, 1997
Article ID: Q164167
The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5

SUMMARY

This article explains some messages that you may receive when working with SQL Security Manager, the causes of the problems, and their resolutions. It applies to situations where the MSSQLServer service starts up with an account that has been granted the Windows NT Server 'Log on as a service' right, and is a member of the local Administrators group.

MORE INFORMATION

I. An attempt to log in to SQL Security Manager is rejected, and the following message returned: 

   This SQL Server does not support Windows NT SQL Server security stored
   procedures.
If the server is set up to use Integrated Security, the following message is encountered: 

   Msg. No.: 18450 Severity: 14 State: 1
   Login failed- User: loginid Reason: Not defined as a valid user of a
   trusted SQL Server connection
Where loginid is the MSSQLServer service startup account.

This problem occurs if system administrator (SA) privileges have previously been revoked from the Administrators group.

II. Within Security Manager, the groups that have been granted user or SA privileges cannot be expanded by double-clicking on them. Attempting to grant user or SA privileges produces the following message: 

   xp_logingrant failure - Unable to query SQL Server security information
This problem occurs as a result of revoking SA privileges on the local Administrators group within the current Security Manager session.

III.

A log on to SQL Security Manager is successful. However, when viewing user or sa privileges, no groups are shown, and the following message appears: 

   No accounts have been granted user authority.
When you attempt to grant user or SA privileges to any group, the following message appears: 

   xp_logingrant failure - Unable to query SQL Server security information
If the server is set up for Integrated Security, a connection is refused, and you receive the following message: 

   You must use the System Administrator's (sa) account when using the SQL
   Security Manager.
These messages indicate that the registry permission (on the key mentioned in the RESOLUTION section of this article) for the local Administrators group has been set to Read.

RESOLUTION

To effectively use SQL Security Manager, SA privileges must be returned to the local Administrators group. Usually, this cannot be achieved with Security Manager. Use the Registry Editor to restore these permissions.

WARNING: Using Registry Editor incorrectly can cause serious, system-wide problems that may require you to reinstall Windows NT to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk.

  1. Open Regedt32.exe

  2. Navigate to the following key: 

          HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer

  3. On the Security menu, click Permissions.

  4. Grant the local Administrators group Full Control of this key.

If the registry key is set properly as outlined above, the following is another message that may be received when attempting to log in to Security Manager: 

   Msg. No.: 18452 Severity: 14 State: 1
   Login Failed- User: sa Reason: Not associated with a Trusted SQL Server
   Connection
This error may occur when the server has been set to use Integrated Security and the default network library does not support trusted connections, such as NWLink IPX/SPX, TCP/IP Sockets or Apple Talk. In this case, you will either have to configure the server for Standard or Mixed Security, or change the default network library to Named Pipes or Multi- Protocol, using the SQL Client Configuration Utility.

Additional query words: grey gray greyed grayed
Keywords : kbenv kbother kbusage SSrvGen
Version : 6.0 6.5
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: April 10, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.