Microsoft KB Archive/258025

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

Article ID: 258025

Article Last Modified on 11/23/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft Windows NT Server 4.0 Standard Edition

-



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.



Steps to Reproduce Behavior
 Install Windows NT 4.0 Server Service Pack 6a as a Primary Domain Controller (PDC). The domain name is DOM1. Computer name is DOM1PDC. Install Windows NT 4.0 Server Service Pack 6a as a member server in the DOM1 domain. Computer name is DOM1SQL. On DOM1SQL, install SQL Server 7.0 Service Pack 2. On DOM1PDC, install only SQL Client Tools. Login into DOM1PDC as the Administrator and create a Local Group called "LocalGroup1" in domain DOM1. On DOM1PDC, create a domain user "DomUser1" and add the user to LocalGroup1.</li>  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 </li> Remove guest access from the Northwind database using the Enterprise Manager interface.</li> Log in as User1 from DOM1PDC.</li> Try to connect to Northwind through Enterprise Manager.</li></ol>

Additional query words: security pdc bdc sql 7.0 primary domain controller backup

Keywords: kbprb kbpending KB258025

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.