?
Solved

Does anyone understand how SQL Server deterimines the database user?

Posted on 2007-04-07
7
Medium Priority
?
201 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 400 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 1600 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

764 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