SIMPLE and wierd SQL Server 2000 login problem

Hi,

I have setup the following server (DomainB\ServerA): SQL Server 2000
8.00.2039 (sp4) on Windows 2003 EE Sp1

Installed and patch a new instance called DAWH. The SQL Server service
is running under "DomainA\sqlserverservice" domain account with
administrator privs on ServerA.

The BUILTIN\Administrators account has been removed

Added a new local windows group called "ServerA\SQLAdminGroup"
Added my domain account "DomainB\chump" (note this a different
domain) to this local windows group "ServerA\SQLAdminGroup"
Run EXEC sp_grantlogin 'ServerA\SQLAdminGroup' [in SQL under SA
login]
Ran EXEC sp_addsrvrolemember 'ServerA\SQLAdminGroup', sysadmin [in
SQL under SA login]

My login has local administrator access on ServerA, so when I log into
windows (under DomainB\chump) and then try and connect via SQL Qry
Manager via windows authentication., I get a LOGIN FAILED for user
"DomainB\chump", even though its been granted sysadmin server role.

I believe this _should_authenticate under via the local windows group
"ServerA\SQLAdminGroup".

HOWEVER, If I remove it and add it directly under the SA account,
EXEC sp_revokelogin 'ServerA\SQLAdmin'
EXEC sp_grantlogin 'DomainB\chump'
EXEC sp_addsrvrolemember 'DomainB\chump', sysadmin

And then log in using DomainB via Windows Authentication thgrough Query
Analyzer, this works ! Im at a loss as to why - perhaps someone could
asist ? This is driving me crazy as Im not trying anything different
from normal (apart from that Ive removed the BUILTIN group!).

Why can I not add my domain group to a local windows group and
provision that with access within SQL Server??????

Thanks in advance for saving my hairline,

J

.

Relevant Pages

  • Re: Problems launching DTS package from Stored Procedure
    ... exec sp_bcp_dbcmptlevel "UsersDatabase" SET FMTONLY ON Select * from Users1 ... Audit Logout ... Here is an example of an UNSUCCESSFUL sql profiler log: ... > I cannot run enterprise manager on our server because it is not installed. ...
    (microsoft.public.sqlserver.dts)
  • Re: Error in Distribution Clean Up Agent
    ... exec master.dbo.sp_configure 'xp_cmdshell' ... Server 1 SQL 2000 Distributor, ... Miscellenaous agent...except the Distribution Clean Up Agent ...
    (microsoft.public.sqlserver.replication)
  • MailMerge API Trouble
    ... word doc set up using the mailmerge wizard that creates an SQL ... Server .odc file to use as a datasource. ... there is an exec sp_cursoropen ...
    (microsoft.public.word.vba.general)
  • Re: Cluster setup failing
    ... Connecting to Server ... ... EXEC sp_grantlogin N'BUILTIN\Administrators' ... SQL Server has been successfully configured! ...
    (microsoft.public.sqlserver.setup)
  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
Loading