Solved

Does anyone understand how SQL Server deterimines the database user?

Posted on 2007-04-07
7
198 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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