Solved

Does anyone understand how SQL Server deterimines the database user?

Posted on 2007-04-07
7
194 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore 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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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