Users registered in SQL Server as part of a local group on PDC/BDC are unable to connect (258025)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
  • Microsoft Windows NT Server 4.0
This article was previously published under Q258025

SYMPTOMS

When a user attempts to connect to SQL Server using SQL Server Enterprise Manager, the following error messages may be displayed:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'publishers', database 'pubs', owner 'dbo'.

-or-

Server: Msg 18456, Level 16, State 1
(Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user DOM1\DomUser1
This problem only occurs with a Windows NT domain user who belongs to a local NT group created on a primary domain controller (PDC) or backup domain controller (BDC), when the group is registered in SQL Server 7.0 installed on a member server.

WORKAROUND

To work around this problem, perform the following steps:
  1. Create a global group in the domain named "GlobalGroup".
  2. Add DomUser1 to GlobalGroup.
  3. Create a local group on DOM1SQL named "SQLLocalGroup".
  4. Add GlobalGroup to SQLLocalGroup.
  5. Use sp_grantlogin to add SQLLocalGroup to SQL Server, and then use sp_grantdbaccess to set the appropriate permissions to the group.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Install Windows NT 4.0 Server Service Pack 6a as a Primary Domain Controller (PDC). The domain name is DOM1. Computer name is DOM1PDC.
  2. Install Windows NT 4.0 Server Service Pack 6a as a member server in the DOM1 domain. Computer name is DOM1SQL.
  3. On DOM1SQL, install SQL Server 7.0 Service Pack 2.
  4. On DOM1PDC, install only SQL Client Tools.
  5. Login into DOM1PDC as the Administrator and create a Local Group called "LocalGroup1" in domain DOM1.
  6. On DOM1PDC, create a domain user "DomUser1" and add the user to LocalGroup1.
  7. Login as Administrator into DOM1SQL and run the following queries in Query Analyser:
    sp_grantlogin 'DOM1\LocalGroup1' 
go
use Northwind
go
sp_grantdbaccess 'DOM1PDC\LocalGroup1'
go
  8. Remove guest access from the Northwind database using the Enterprise Manager interface.
  9. Log in as User1 from DOM1PDC.
  10. Try to connect to Northwind through Enterprise Manager.
Modification Type:MinorLast Reviewed:11/23/2005
Keywords:kbpending kbprb KB258025
©2004 Microsoft Corporation. All rights reserved.