xp_grantlogin 'Administrators','admin'

xp_grantlogin 'Administrators','admin'

Post by Michael Bevilacqu » Wed, 13 May 1998 04:00:00



I have all but 1 of 7 SQL Server boxes uses Integrated Security. The last
one is being a bit of a pain. I went into SQL Security Manager and noticed
that no NT groups or accounts were trusted. I attempted to add NT group
"Administrators" as sa, but keep receiving error:

xp_logingrant failure - Unable to query SQL Server security information.

I ran SQL trace and obtained the exact SQL commands being run. The error
displayed in SQL Security Manager is incorrect because the extended stored
procedure called is xp_grantlogin, not xp_logingrant as shown in error
message. Anyhow, this is what I ran and the results from an ISQL/w window
logged in as 'sa'.

Command run:  xp_grantlogin 'Administrators','admin'.
Results            :  Unable to query SQL Server security information.

Any ideas?

Cheers,
Michael

 
 
 

xp_grantlogin 'Administrators','admin'

Post by Tibor Karasz » Thu, 14 May 1998 04:00:00


Michael,

This might be a permission problem to the registry. I'm not quite sure, but
it should/might be under HKLM\SW\MS\MSSQLServer somewhere.

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB


>I have all but 1 of 7 SQL Server boxes uses Integrated Security. The last
>one is being a bit of a pain. I went into SQL Security Manager and noticed
>that no NT groups or accounts were trusted. I attempted to add NT group
>"Administrators" as sa, but keep receiving error:

>xp_logingrant failure - Unable to query SQL Server security information.

>I ran SQL trace and obtained the exact SQL commands being run. The error
>displayed in SQL Security Manager is incorrect because the extended stored
>procedure called is xp_grantlogin, not xp_logingrant as shown in error
>message. Anyhow, this is what I ran and the results from an ISQL/w window
>logged in as 'sa'.

>Command run:  xp_grantlogin 'Administrators','admin'.
>Results            :  Unable to query SQL Server security information.

>Any ideas?

>Cheers,
>Michael


 
 
 

xp_grantlogin 'Administrators','admin'

Post by Brian Mora » Fri, 15 May 1998 04:00:00


PSS ID Number: Q164167
Article last modified on 04-10-1997

6.0 6.5

WINDOWS

======================================================================
-------------------------------------------------------------------------
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
============================================================================
=
Copyright Microsoft Corporation 1997.

--
Brian Moran
Chief Architect
Spectrum Techology Group
SQLServer MVP, MCSE, MCSD, MCT

Check out my monthly SQL column
in Windows NT Magazine!


>I have all but 1 of 7 SQL Server boxes uses Integrated Security. The last
>one is being a bit of a pain. I went into SQL Security Manager and noticed
>that no NT groups or accounts were trusted. I attempted to add NT group
>"Administrators" as sa, but keep receiving error:

>xp_logingrant failure - Unable to query SQL Server security information.

>I ran SQL trace and obtained the exact SQL commands being run. The error
>displayed in SQL Security Manager is incorrect because the extended stored
>procedure called is xp_grantlogin, not xp_logingrant as shown in error
>message. Anyhow, this is what I ran and the results from an ISQL/w window
>logged in as 'sa'.

>Command run:  xp_grantlogin 'Administrators','admin'.
>Results            :  Unable to query SQL Server security information.

>Any ideas?

>Cheers,
>Michael