Re: Permission question - another one
From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 08/15/04
- Next message: Adam Machanic: "Re: Adam M., about your -PIVOT concerns-"
- Previous message: Steve Kass: "Re: Query Designer Error message - for UPDATE sequence"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Next in thread: Kalen Delaney: "Re: Permission question - another one"
- Reply: Kalen Delaney: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 15 Aug 2004 13:03:02 -0700
"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: Adam Machanic: "Re: Adam M., about your -PIVOT concerns-"
- Previous message: Steve Kass: "Re: Query Designer Error message - for UPDATE sequence"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Next in thread: Kalen Delaney: "Re: Permission question - another one"
- Reply: Kalen Delaney: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
