Re: Permission question - another one
From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 08/18/04
- Next message: Jami Bradley: "Re: Profiling/Optimizing complex queries"
- Previous message: Erik Grob: "Re: why default index for primary key is clustered?"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Next in thread: Thomas Scheiderich: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 17 Aug 2004 18:48:04 -0700
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:u30Omu6gEHA.3944@tk2msftngp13.phx.gbl...
> I'm getting confused now. Why what?
>
> > 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, I cannot connect. I would have thought that
Administrator
> > would have been able to connect, before tfs.
>
> What does it mean that tfs 'has only' debugger users and users? There is
> nothing called debugger users in SQL Server.
> Are you asking why is it connecting to SQL Server?
> When you say 'log on as Administrator', do you mean log on to the local
> Windows machine, or something else?
> What tool(s) are you using to connect to SQL Server?
>
> Perhaps you should start a new thread, so we're not quite so deep and
other
> people can add info related to the OS login.
OK.
I'll put the previous post as well as your remarks in a new thread.
Thanks,
Tom.
>
> Include this information (among the usual info):
>
> What exact name you are using on the OS (<domain or machine\usernmae>) ?
> What exact name you are using once you connect to SQL Server (select
> suser_sname() from Query Analyzer) ?
> What is the output of sp_helplogins on your SQL Server?
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> news:10hvfvapgj8kvae@corp.supernews.com...
> > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > news:%237HIjNogEHA.3676@TK2MSFTNGP12.phx.gbl...
> > >
> > > So these are two different Windows users and two different logins as
> far
> > as
> > > SQL Server is concerned. TRAVAC/tfs has not been granted access to SQL
> > > Server, and Raptor/tfs has been granted access. Just because the
second
> > part
> > > of the name is the same, there is nothing to connect these two names
to
> > each
> > > other.
> >
> > I understand that.
> >
> > My question is why????
> >
> > 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.
> >
> > 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, I cannot connect. I would have thought that
Administrator
> > would have been able to connect, before tfs.
> >
> > Tom.
> >
> > The question is
> > >
> > > --
> > > HTH
> > > ----------------
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > news:10hto5j5q8pj1b1@corp.supernews.com...
> > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > > > news:#7AjFTjgEHA.556@tk2msftngp13.phx.gbl...
> > > > > These questions have to do with how you log in to the OS, which is
> > > getting
> > > > > away from my areas of expertise.
> > > > > I do know that if you are logging into a machine, even if that
> machine
> > > is
> > > > > not part of a particular domain, if the machines are set up with
the
> > > right
> > > > > permissions, you can log in as a domain user.
> > > > > You will have to figure out what your OS login name is, because
that
> > is
> > > > what
> > > > > SQL Server looks at when you try to connect using Windows
> > > Authentication.
> > > > I
> > > > > believe you should be able to see it from Cntl-Alt-Del; it will
show
> > you
> > > > who
> > > > > you are logged in as. Again, it is that OS login name that SQL
> Server
> > > > uses,
> > > > > and must have been granted access to SQL Server if you're using
> > Windows
> > > > > Authentication. And whatever that login name is will determine how
> > much
> > > > > privilege you have on SQL Server.
> > > > >
> > > >
> > > > I understand that, now. I do have an interesting setup where,
always
> > > > connecting as Windows Authentication, if I connect from a machine in
> the
> > > > same Domain - tfs (TRAVAC/tfs), I get an error that says "Login
Failed
> > for
> > > > user TRAVAC/tfs".
> > > >
> > > > But if I connect from a 2 other machines that are not part of the
> > Domain -
> > > > it shows me as connecting as RAPTOR/tfs and it works fine (Raptor is
> the
> > > > Machine the Sql Server is on - not the names of the workstations
> > > > connecting).
> > > >
> > > > Very strange.
> > > >
> > > > > How you end up with one OS login name or another is a question for
> > > someone
> > > > > else to answer. I'm sorry.
> > > >
> > > > That's fine. It is getting a little strange. I really appreciate
the
> > > help.
> > > > It does make a lot more sense now (except for the couple of things I
> > > already
> > > > mentioned).
> > > >
> > > > Thanks,
> > > >
> > > > Tom.
> > > > >
> > > > > --
> > > > > HTH
> > > > > ----------------
> > > > > Kalen Delaney
> > > > > SQL Server MVP
> > > > > www.SolidQualityLearning.com
> > > > >
> > > > >
> > > > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > > > news:10hsgvnsraimef4@corp.supernews.com...
> > > > > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > > > > news:10hsf4kj7ntju32@corp.supernews.com...
> > > > > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in
message
> > > > > > > news:#QD4ALbgEHA.712@TK2MSFTNGP09.phx.gbl...
> > > > > > > > Does your Windows Login TRAVAC/tfs belong to the local
> > > > Administrators
> > > > > > > group?
> > > > > > > > As you told us below, that NT GROUP has been granted rights
to
> > SQL
> > > > > > Server,
> > > > > > > > and is most likely in the sysadmin SQL Server role, so that
> > > anybody
> > > > in
> > > > > > > that
> > > > > > > > role uses the username dbo in any database.
> > > > > > >
> > > > > > > Are you talking about on Raptor (where the Sql Server I am
> > > accessing)?
> > > > > > >
> > > > > > > Where would I find this? I assume you are talking NT user
group
> > and
> > > > not
> > > > > > Sql
> > > > > > > Server groups - Correct?
> > > > > > >
> > > > > > > As far as I can find - I don't have TRAVAC/tfs defined
anywhere.
> > > > Would
> > > > > > that
> > > > > > > be the BUILTIN/Administrators that is one of the 5 I found
below
> > > (not
> > > > > > really
> > > > > > > sure what this one is for)?
> > > > > > >
> > > > > > > I figured that that somewhere TRAVAC/tfs was granted rights -
I
> > just
> > > > > can't
> > > > > > > find where.
> > > > > >
> > > > > > I am assuming local administrator is in local users and groups
> from
> > > > > > Administrative Tools in the Control panel. It turns out that
when
> I
> > > did
> > > > > > take administrative priveledges away from tfs on RAPTOR, it
won't
> > > allow
> > > > me
> > > > > > to log on anymore from Windows Authentication (which is what I
> would
> > > > > > expect).
> > > > > >
> > > > > > However, on my other machine - Dino - tfs has administrative
> > > > priviledges,
> > > > > > but it is a workgroup and not part of the domain TRAVAC. Why
can
> it
> > > > > connect
> > > > > > and why does it have the user name RAPTOR/tfs and not DINO/tfs.
> > > > > >
> > > > > > Also, if Dino is not part of the TRAVAC domain (which Raptor
is),
> > why
> > > > does
> > > > > > Raptor allow it in? I know it has administrative priviledges,
but
> > > they
> > > > > are
> > > > > > for Dino. I assume that just because you let someone in from
> > another
> > > > > domain
> > > > > > (or a workgroup), you don't allow the administrative priviledges
> to
> > > come
> > > > > > over (unless you specifically allow that) - right?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Tom.
> > > > > > >
> > > > > > > Tom
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > > ----------------
> > > > > > > > Kalen Delaney
> > > > > > > > SQL Server MVP
> > > > > > > > www.SolidQualityLearning.com
> > > > > > > >
> > > > > > > >
> > > > > > > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > > > > > > news:10hqdek681ehucc@corp.supernews.com...
> > > > > > > > > "Tim S" <stahta01@juno.com> wrote in message
> > > > > > > > > news:10hbhjs4f57ph12@corp.supernews.com...
> > > > > > > > > > Thomas:
> > > > > > > > > >
> > > > > > > > > > You might see if running SELECT SYSTEM_USER,
SESSION_USER,
> > > USER,
> > > > > > > > > > CURRENT_USER
> > > > > > > > > > It could help you to understand the difference.
> > > > > > > > > >
> > > > > > > > > > Note: You once said you removed yourself from the
> > > administrators
> > > > > > > group,
> > > > > > > > > the
> > > > > > > > > > removal most likely would NOT take affect till your
logged
> > out
> > > > and
> > > > > > > > logged
> > > > > > > > > > back on. You can test this by trying to re-add you to
the
> > > > > > > Administrator
> > > > > > > > > > group if it lets you then you are still administrator.
> > > > > > > > >
> > > > > > > > > Right. As a matter a fact, I took myself (tfs) out of all
> of
> > > the
> > > > > > Server
> > > > > > > > > Roles just for my testing.
> > > > > > > > >
> > > > > > > > > There is no user defined my Sql Server as "TRAVAC/tfs"
(even
> > > > though
> > > > > a
> > > > > > > > couple
> > > > > > > > > of my databases show TRAVAC/tfs as the owner - when I
right
> > > click
> > > > > the
> > > > > > > > > database in EM).
> > > > > > > > >
> > > > > > > > > For my test here, I connected using Windows Authentication
> > > > > (therefore
> > > > > > I
> > > > > > > am
> > > > > > > > > shown as TRAVAC/tfs - if I log on with SQL
Authentication -
> I
> > am
> > > > > shown
> > > > > > > as
> > > > > > > > > tfs).
> > > > > > > > >
> > > > > > > > > Unless I am mistaken, I was told that tfs and TRAVAC/tfs
are
> > > > > different
> > > > > > > > users
> > > > > > > > > with different permissions. Here is what I have when I do
> the
> > > > above
> > > > > > > > selects
> > > > > > > > > (as well as some Kalen asked me to do):
> > > > > > > > >
> > > > > > > > > suser_sname() = TRAVAC/tfs
> > > > > > > > > user_name() = dbo
> > > > > > > > > current_user = dbo
> > > > > > > > > user = dbo
> > > > > > > > > system_user = TRAVAC/tfs
> > > > > > > > > session_user = dbo
> > > > > > > > >
> > > > > > > > > Now, TRAVAC/tfs is not defined (and was never defined as a
> > > user).
> > > > > If
> > > > > > I
> > > > > > > > look
> > > > > > > > > at my users I see:
> > > > > > > > > BUILTIN/Administrators, josef, sa, TRAVAC\jon.
> > > > > > > > >
> > > > > > > > > Why am I begin shown as dbo of VDW?
> > > > > > > > >
> > > > > > > > > If I log on using Sql Authentication as tfs, I get the
> > > following:
> > > > > > > > >
> > > > > > > > > suser_sname() = tfs
> > > > > > > > > user_name() = tfs
> > > > > > > > > current_user = tfs
> > > > > > > > > user = tfs
> > > > > > > > > system_user = tfs
> > > > > > > > > session_user = tfs
> > > > > > > > >
> > > > > > > > > I understand the Sql Authentication results. I don't
> > understand
> > > > the
> > > > > > > > Windows
> > > > > > > > > Authentication results (TRAVAC/tfs). Why is it dbo and
why
> do
> > I
> > > > get
> > > > > > > > access
> > > > > > > > > at all? Somewhere outside of Sql Server must be giving me
> > > access,
> > > > I
> > > > > > > > assume.
> > > > > > > > > I just can't seem to figure out where.
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Tom.
> > > > > > > > > >
> > > > > > > > > > Tim S
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Jami Bradley: "Re: Profiling/Optimizing complex queries"
- Previous message: Erik Grob: "Re: why default index for primary key is clustered?"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Next in thread: Thomas Scheiderich: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
