Re: Permission question - another one

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 08/14/04 

Date: Sat, 14 Aug 2004 12:27:31 -0700

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.

How you end up with one OS login name or another is a question for someone
else to answer. I'm sorry. 

-- 
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Permission question - another one
    ... >> As you told us below, that NT GROUP has been granted rights to SQL ... > Are you talking about on Raptor (where the Sql Server I am accessing)? ... I am assuming local administrator is in local users and groups from ...
    (microsoft.public.sqlserver.programming)
  • Re: Permission question - another one
    ... I would have thought that Administrator ... Are you asking why is it connecting to SQL Server? ... > and I gave BUILTIN/Administrator this database as its default database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Permission question - another one
    ... it is that OS login name that SQL Server ... >> I am assuming local administrator is in local users and groups from ... >> take administrative priveledges away from tfs on RAPTOR, ...
    (microsoft.public.sqlserver.programming)
  • Re: Windows Vista Enterprise and SQL Server 2005 Agent
    ... This has nothing to do with SQL Server and this is an expected behaviour of Windows Vista operating systems. ... Cause of those popups and normal-user-like acting is some new security system called User Account Control. ... It all seems to be related to security things, even though I am an administrator. ...
    (microsoft.public.sqlserver.security)
  • Re: Fresh installation and no access
    ... You're not getting access because you're not an administrator when you log ... In previous OS's you had administrator permissions on the database ... You must create a login (to allow connect to SQL Server) then a user to be ...
    (microsoft.public.sqlserver.security)
Loading