Solved

Does anyone understand how SQL Server deterimines the database user?

Posted on 2007-04-07
7
192 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
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 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now