Solved

Does anyone understand how SQL Server deterimines the database user?

Posted on 2007-04-07
7
197 Views
Last Modified: 2008-01-09
I use Windows authentication on my SS 2005 Vehicles database.   There is a login called "Service" which is mapped to user "Service" for the Vehicles database.  There is another login called "Sales" which is mapped to user "Sales" for the Vehicles database.  

There are Windows groups for both "Service" and "Sales". Windows account "Ralph" is a member of both.  Ralph does not have an individual SQL Server login, but Ralph can connect to Vehicles, nonetheless, because of his group membership.

Once connected, I presume Ralph gets the collective server permissions of both groups.  Is that correct? But what is Ralph's user context at the database level?  Does he run as "Service" or does he run as "Sales"?   And, does he get the collective database permissions of both users or just the permissions of one user context?

Please don't answer: do "Select USER" to find out.  I'm interested in understanding HOW SS 2205 resolves the database user when different group logins map to different users.










 
0
Comment
Question by:dqmq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 18871516
In general the way windows resolves this is the most restrictive settings win.

Therefore, if Sales had read permissions defined for table A and Service had deny read defined, then Ralph would be denied read access to table A.

I am not 100% sure this is how it works in SQL 2005 as I have not tested this (but you could easily), but this is how it worked previously and how it works for windows in general.  I am not sure why SQL 2005 would be any different.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 18871868
> There is a login called "Service"
is that a Windows Authentication-based SQL Login, or a plain SQL login?
if it is a Windows Based login, it should be named DOMAIN\USER resp. DOMAIN\GROUP...

now, a windows user that does a login based on windows authentication, will pass on login it's windows account sid, along with the list of group it is part of (at that time). sql server will then accumulate all the logins that match.
accumulated will be all the users in all the databases mapped to those logins

0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 400 total points
ID: 18871979
Ralph will be user Ralph in the database (unless he is a local admin in which case he will be dbo and the groups will be ignored).
As to permissions he will inherit permissions from both groups i.e will get all the grants from both groups (and public).
Any deny permissions in either group will overrde a grant in the other.

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18872534
>>Ralph does not have an individual SQL Server login, but Ralph can connect to Vehicles, nonetheless, because of his group membership. <<
How is this possible?  Is Ralph using SQL Server Authentication or Windows Authentication?  Either way he needs to have a login.
0
 
LVL 42

Author Comment

by:dqmq
ID: 18873282
Angel,
You are correct, the names take the form "domain\group". I was just changed them to simplify the question and  to protect the innocent.

As I understand your answer, the session will have the collective permissions of ALL users mapped to ANY of the logins that the principal is a member of.  So, Ralph will have the server permissions of both the Service and Sales logins and the database permissions of both the Service and Sales users.

Nigel,
Your answer completes the puzzle.  SELECT USER returns "Ralph", even though there is no such database user defined! Ralph gets the database permissions of Service, Sales, and Public  (with any deny permissions overiding all grant permissions).   Most importantly, if Ralph is a local admin, he assumes the user dbo and group particpation is ignored.  Thanks so much clearing that up.

AcPerkins,
Indeed, Ralph can login using Windows Authentication without a personal login. He need only belong to a windows group that has an Login.  That is one of the reasons that Windows Authentication can simplify security administration. The essence of my question was about how SS2005 resolves the USER and the permissions when multiple group logins apply.

0
 
LVL 35

Expert Comment

by:mrichmon
ID: 18877039
>>Ralph gets the database permissions of Service, Sales, and Public  (with any deny permissions overiding all grant permissions).

Isn't that what I said in the very first response?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18877266
Thanks for clarifying.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question