Re: Permission question - another one
From: Thomas Scheiderich (tfs_at_deltanet.com)
Date: 08/09/04
- Next message: mak: "RE: Query read + update"
- Previous message: Itzik: "IIS SUPPORT"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 8 Aug 2004 23:47:55 -0700
I am still looking at this post and your last one and I am getting closer to
getting it.
If I add an Sql Login it does add the TRAVAC\ in front of the names, if I am
setting them up as Windows Au
But I do have some that are showing without the Domain in front of it. This
seems to be users that were setup to use SQL Server Authentication. If I
use Windows Authentication - it needs to be able to see it and it will add
the Domain to it.
This was part of what was making it confusing (as you mentioned before),
whether it showed TRAVAC/tfs or RAPTOR/tfs or just tfs, seemed to depend on
which machine I logged on from and whether the workstation was part of a
domain or not.
If I connected from a workstation that was not part of a domain and I was
using Windows Authentication, it seemed to put RAPTOR/ in front
(RAPTOR/tfs). RAPTOR is the Server that has SQL Server running on it.
If I connected from the same machine (remember not part of domain) and used
Sql Authentication to connect, it just used tfs.
Tom.
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:O#EuUZNfEHA.3204@TK2MSFTNGP10.phx.gbl...
> I answered this in the my direct reply to Aaron's post. There are two
> different logins we're talking about here.
>
> When you say this:
> " I would have thought that Sql Server would have prevented me from access
> to
> that database, if I didn't give it permission, under both Windows and Sql
> Authentication - otherwise what good is it."
>
> ... it seems like you are assuming the Windows login and the SQL login are
> the same login. There is no such thing.
>
>
> Every login name is EITHER a windows authenticated login or a SQL login.
>
> If when you connect to SQL Server, you say to connect using Windows
> authentication, the ONLY login name you can use is one that exactly
matches
> your NT name, I believe that would be raptor\tfs for you.
>
> If you choose to connect using SQL authentication, you can enter any login
> name and password that you know.
>
> Again, the Windows login raptor\tfs and the SQL login tfs are two
different
> logins as far as SQL Server is concerned. There are two different rows in
> sysxlogins for these names, and they each have different roles and
> permissions.
>
> You say this:
> " I could think I am taking permissions away from someone, and then find
> that
> all they had to do was get onto Query Analyser - say Connect via Windows
> Authentication and bang - they are in."
>
> I know it's confusing that the names are similar, but they are different
> logins. As far as SQL Server is concerned, they are different 'people'.
>
> It would be as meaningful to say,
> "I think I am taking permissions away from Joe, but all he has to do is
use
> Query Analyzer and connect as 'sa' and bang, he's in!'
> Yes, of course, if he knows the sa password, he can get around any
> permission restrictions you've set for Joe. Joe and sa are two different
> logins.
>
> If you have permission restrictions for some login tfs that's fine, but by
> giving permissions to a Windows login raptor\tfs, you're saying that
anyone
> connecting to NT as raptor\tfs can use the server with more permissions.
If
> you don't want someone connecting as raptor\tfs to have a lot of
> permissions, you can remove raptor\tfs from having access to the server or
> remove his permissions.
>
> Not everyone who has a login name, like Joe, can also get in as
> NTdomain\joe. It is not a wide open back door. Someone had to allow
> NTdomain\joe access to the server, and the user has to be able to login to
> NT as NTdomain\joe. So that person has to know Joe's NT password in
addition
> to the SQL Server administrator allowing NTdomain\joe to have access.
>
> There is nothing in Windows that overrides SQL Server permissions. There
are
> two different login names here, one has more permission set by the SQL
> administrator, and one has fewer.
> You as a human user, can choose to connect as either of the two, but ONLY
> because there are two different logins available to you. Most users will
> only have one way to get into the server. They will either be given a SQL
> login and password, or their NTdomain\username will be given access to the
> SQL Server.
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> news:10hanak41gpmbef@corp.supernews.com...
> > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > news:#p1zXELfEHA.644@tk2msftngp13.phx.gbl...
> > > Thomas
> > >
> > > So it looks like tfs has NOT been added with a user name in the vdw
> > > database.
> > > This user has been added as a user in master, and that is usually not
a
> > good
> > > idea. Most logins should not have specific user names in master. I
> suggest
> > > you remove tds as a user from master (sp_revokedbaccess), and add tfs
as
> a
> > > user in vdw (sp_grantdbaccess).
> > >
> >
> > I agree about the master database. I am the only user on my system,
which
> > is at home. I am just trying to understand why certain things happen.
> >
> > In this case, as you said, tfs is not in vdw. I specifically took it
out.
> > I also took tfs out of all the server roles. This is why when I try to
> > connect (using isql or Query analyser) using Sql Authentication, it
won't
> > even let me connect to vdw (use vdw) gives me an error.
> >
> > But with Windows Authentication (and this is from the local machine or a
> > remote machine), I can connect as tfs and not only connect to vdw, but I
> can
> > select and update the tables in vdw.
> >
> > I was curious about this, because I normally use Sql Authentication
> myself,
> > but at my clients site, they use Windows Authentication and I am trying
to
> > understand the difference.
> >
> > I thought I did, until this.
> >
> > I would have thought that Sql Server would have prevented me from access
> to
> > that database, if I didn't give it permission, under both Windows and Sq
l
> > Authentication - otherwise what good is it.
> >
> > I could think I am taking permissions away from someone, and then find
> that
> > all they had to do was get onto Query Analyser - say Connect via Windows
> > Authentication and bang - they are in.
> >
> > I am sure I am missing something. I assume it has something to do with
> > rights given by Windows that override Sql Servers, but I don't know what
> it
> > is.
> >
> > Thanks,
> >
> > Tom.
> > >
> > > --
> > > HTH
> > > ----------------
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > news:10ha6u8edt0ne6d@corp.supernews.com...
> > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > > > news:eHaONDDfEHA.3200@TK2MSFTNGP09.phx.gbl...
> > > > > Where would you find what? Woudn't what be defined in EM?
> > > > >
> > > > > And which one is 'you'? I thought you could use both of these
> logins?
> > > >
> > > > Sorry, my mistake. I was looking for where I would find the user
name
> > in
> > > EM
> > > > (I think - now I am confused)
> > > >
> > > > >
> > > > > Unfortunately, there is no good one to see all the permissions has
> > and
> > > > all
> > > > > the roles that a login belongs to in a single statement. And I
> rarely
> > > use
> > > > > EM.
> > > > >
> > > > > I would try going to the vdw database, and running sp_helpuser.
Let
> us
> > > > know
> > > > > what output you get fior tfs and raptor\tfs
> > > >
> > > > Here is the output for sp_helpuser when I am in the vdw database as
> well
> > > as
> > > > in master (just to make sure I was doing it correctly).
> > > >
> > > > For VDW
> > > >
> > > > sp_helpuser
> > > >
> > > > UserName GroupName
> LoginName
> > > > DefDBName UserID SID
> > >
> >
>
> -------------------------- ---------------------------------- ---------- -
> > > --
> > >
> >
>
> --------- ------ ---------------------------------------------------------
> > > --
> > >
> >
>
> --------------------------------------------------------------------------
> > > --
> > > > -------------------------------------
> > > > dbo db_owner sa
> > > > master 1 0x01
> > > > josef the dog public josef
> > > > master 6 0x96F72ADEEDFA5D44A9121C1A999C547B
> > > > SQLExec db_owner NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_accessadmin NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_securityadmin NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_ddladmin NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_backupoperator NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_datareader NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > > SQLExec db_datawriter NULL
> > > > NULL 5
> > > > 0x0105000000000005150000002637E102DE6C0F23DF555D3E00130000
> > > >
> > > >
> > > >
> > > > sp_helpuser tfs
> > > >
> > > > Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line
189
> > > > The name supplied (tfs) is not a user, role, or aliased login.
> > > >
> > > >
> > > > For master
> > > >
> > > > sp_helpuser tfs
> > > >
> > > > UserName GroupName LoginName DefDBName UserID SID
> > >
> >
>
> -------- ------------ --------- ---------------- ------ ------------------
> > > --
> > >
> >
>
> --------------------------------------------------------------------------
> > > --
> > >
> >
>
> --------------------------------------------------------------------------
> > > --
> > > > tfs public tfs tomsData 5
> > > > 0x93588F7930ED964B850268196545251C
> > > >
> > > >
> > > >
> > > > Thanks
> > > >
> > > > Tom
> > > > >
> > > > > --
> > > > > HTH
> > > > > ----------------
> > > > > Kalen Delaney
> > > > > SQL Server MVP
> > > > > www.SolidQualityLearning.com
> > > > >
> > > > >
> > > > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > > > news:10h8grp7f4hcia2@corp.supernews.com...
> > > > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > > > > > news:eEIiG5$eEHA.2812@tk2msftngp13.phx.gbl...
> > > > > > > Aaron
> > > > > > >
> > > > > > > Thomas and I have been having a long discussion about this,
and
> I
> > am
> > > > > > trying
> > > > > > > to help him understand the different in terminology between
SQL
> > > Server
> > > > > > login
> > > > > > > and a user in a database. The fact that we use the term 'user'
> to
> > > talk
> > > > > > about
> > > > > > > names in NT doesn't make things any easier.
> > > > > > >
> > > > > > > Thomas
> > > > > > >
> > > > > > > Raptor\tfs is a windows authenticated login and tfs is a SQL
> > Server
> > > > > > > authenticated login. As Aaron says, they are two different
> things.
> > > > Each
> > > > > > has
> > > > > > > its own row in syslogins and its own unique identifier (SID).
> SQL
> > > > Server
> > > > > > > does not connect them to each other and treat them as the same
> > just
> > > > > > because
> > > > > > > they both have 'tfs' as part of the name. Obviously Raptor\tfs
> has
> > > > more
> > > > > > > permissions.
> > > > > >
> > > > > > Where would I find that?
> > > > > >
> > > > > > Wouldn't that be defined in EM?
> > > > > >
> > > > > > I did look in User Manager for Domains and found that I have
just
> > > about
> > > > > > everything. I tried taking myself out of the Administrators
role,
> > but
> > > I
> > > > > > could still access vdw.
> > > > > >
> > > > > > Tom
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > > ----------------
> > > > > > > Kalen Delaney
> > > > > > > SQL Server MVP
> > > > > > > www.SolidQualityLearning.com
> > > > > > >
> > > > > > >
> > > > > > > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message
> > > > > > > news:%23PAxGj$eEHA.1888@TK2MSFTNGP10.phx.gbl...
> > > > > > > > Raptor\tfs is a Windows user.
> > > > > > > > tfs is a SQL user.
> > > > > > > >
> > > > > > > > These are NOT the same thing.
> > > > > > > >
> > > > > > > > --
> > > > > > > > http://www.aspfaq.com/
> > > > > > > > (Reverse address to reply.)
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Thomas Scheiderich" <tfs@deltanet.com> wrote in message
> > > > > > > > news:10h7qeb7h8uocd9@corp.supernews.com...
> > > > > > > > > In Sql Server 2000
> > > > > > > > >
> > > > > > > > > I am tfs as a user and login.
> > > > > > > > >
> > > > > > > > > I have no server roles.
> > > > > > > > >
> > > > > > > > > I have no access to the vdw database (dbo is sa).
> > > > > > > > >
> > > > > > > > > If, using query analyser, I try to connect, using Sql
Server
> > > > > > > > > Authentication - I cannot connect to vdw (can't issue "use
> > vdw"
> > > or
> > > > > > > choose
> > > > > > > > > vdw from the drop down box) - I get the message "tfs is
not
> a
> > > > valid
> > > > > > user
> > > > > > > > in
> > > > > > > > > the database vdw".
> > > > > > > > >
> > > > > > > > > I am logged onto my workstation as tfs.
> > > > > > > > >
> > > > > > > > > If I try to connect from query analyser, using Windows NT
> > > > > > authentication
> > > > > > > > > (which shows "Rapter.Master.Raptor\tfs" in the titlebar -
so
> I
> > > am
> > > > > > logged
> > > > > > > > as
> > > > > > > > > tfs), it allows me, not only to connect to VDW and do
> selects,
> > > but
> > > > > it
> > > > > > > also
> > > > > > > > > allows me to update.
> > > > > > > > >
> > > > > > > > > What in NT allows me to override the Sql Permissions?
> > > > > > > > >
> > > > > > > > > Thanks,
> > > > > > > > >
> > > > > > > > > Tom.
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: mak: "RE: Query read + update"
- Previous message: Itzik: "IIS SUPPORT"
- In reply to: Kalen Delaney: "Re: Permission question - another one"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
