I have been trying to figure out permissions and how they work on my Sql
Server. I have gotten quite a bit of help and think I understand most of it
pretty well, but am having trouble understanding why this is happening.

I have a Domain - Travac.
My Sql Server 2000 is on Raptor - which is part of the Travac Domain.
I have a workstation - Dino - which is not part of the Travac Domain.
I have 2 users: tfs and jon.
tfs is member of Debugger Users and Users
jon is member of Adminstrators and Users.

Neither is defined in Raptor Sql Server (as far as I can tell). As I said,
I assume that if you are not part of the domain, you will get the Servers
name as your domain (Raptor/tfs or Raptor/jon).

In RAPTOR I have these users defined in EM.

BUILTIN/Administrators
josef
sa
tfs
TRAVAC/jon

I found out that Raptor/tfs is being allowed access through the
BUILTIN/Administrators account and has the name of dbo. I know this because
I had created a new database called NewNorthwind for some work I was doing
and I gave BUILTIN/Administrator this database as its default database.
When I connect, this is the database it defaults to.

The problem is that tfs (on Dino which is the machine I am connecting from),
has only "Debugger Users" and Users. Why is it connecting? If I log on as
Administrator or as Jon (which is part of the Administrator group), I cannot
connect. I would have thought that Administrator
would have been able to connect, before tfs (which is not a member of
Administrator).

Kalen asked me the following questions from my other thread:

[quoted text, click to view]

These are NT groups. I am using Windows NT Authentication and this machine
is not part of the Travac Domain.

[quoted text, click to view]

Yes. I would think it would not be able to (as it doesn't for "jon"). What
is allowing tfs from Dino connect to Sql Server on Raptor? Why is it using
the BUILTIN/Administrator group? There is no Dino/tfs user defined on the
Sql Server on Raptor.

[quoted text, click to view]
Windows machine, or something else?

I mean logged on to Dino as Administrator.

[quoted text, click to view]

Query Analyser - which allows you to use either Windows Authentication or
Sql Server Authentication.

[quoted text, click to view]

Not sure what you mean here. I am using Windows Authentication which gives
me Raptor/tfs (so I guess that means machine\tfs - but not the machine I am
connecting from but the machine I am connecting to. If Dino was the member
of a domain it would be domain/user or Travac/tfs).

[quoted text, click to view]
suser_sname() from Query Analyzer) ?

Select suser_sname() gives me Raptor/tfs.
Select user_name() gives me dbo. ??????

How can Raptor/tfs be dbo if Raptor/tfs is not even set up in Sql Server.

[quoted text, click to view]

LoginName SID
DefDBName DefLangName AUser ARemote
-------------------------------------------- -------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------- ----------
-------------- -------------------- ----- -------
BUILTIN\Administrators
0x01020000000000052000000020020000
NewNorthWind us_english NO no
josef
0x96F72ADEEDFA5D44A9121C1A999C547B
master us_english yes no
sa 0x01
master NULL yes no
tfs
0x93588F7930ED964B850268196545251C
tomsData us_english yes no
TRAVAC\JON
0x0105000000000005150000001EC98E39EB36CA4760F8C56BFA030000
master us_english NO no

(5 row(s) affected)

LoginName DBName UserName
UserOrAlias
---------- -------------------------- -------------------------------- -----
------
josef VDW josef the dog User
sa master db_owner
MemberOf
sa master dbo User
sa model db_owner
MemberOf
sa model dbo User
sa msdb db_owner
MemberOf
sa msdb dbo User
sa Northwind db_owner
MemberOf
sa Northwind dbo User
sa pubs db_owner
MemberOf
sa pubs dbo User
sa tempdb db_owner
MemberOf
sa tempdb dbo User
sa VDW db_owner
MemberOf
sa VDW dbo User
tfs Bingo db_owner
MemberOf
tfs Bingo tfs User
tfs master tfs User
tfs model tfs User
tfs msdb tfs User
tfs NewNorthWind tfs User
tfs Northwind db_owner
MemberOf
tfs Northwind tfs User
tfs Openworx db_owner
MemberOf
tfs Openworx tfs User
tfs PhoneList tfs User
tfs pubs tfs User
tfs SBS_OLAP tfs User
tfs SBS_OLTP tfs User
tfs SBS_OLTP_COPY tfs User
tfs tempdb tfs User
tfs testtom tfs User
tfs tomsData db_accessadmin
MemberOf
tfs tomsData db_owner
MemberOf
tfs tomsData db_securityadmin
MemberOf
tfs tomsData tfs User
tfs VDW db_owner
MemberOf
tfs VDW tfs User

(38 row(s) affected)

If tfs which is defined on DINO ( your client ) has the same password as the
tfs login (defined in RAPTOR), you should get in.... I suspect also that tfs
on RAPTOR is a local administrator which explains why tfs on DINO is treated
as a SQL Admin..

Get rid of the tfs login on RAPTOR and I suspect tfs from DINO can no
longer get in.....

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]
User

[quoted text, click to view]

I tried to change the password for tfs on Raptor using the User Managers for
Domain and it still connects from Dino. I also deleted it from the Domain
and it still connects? Besides it is connecting using the tfs user on
Raptor but the BUILTIN/Administrator user.

This is really making no sense.

Tom
[quoted text, click to view]