locked
sql login vs database user RRS feed

  • Question

  • I have just read endless websites on sql logins and database users and none have answered my basic question.

    Firstly, we are using SQL in an AD environment so forget SQL logins. Everything is windows accounts.

    I get that SQL login is 'Server' and database user is for database objects.

    So let's say I have multiple applications accessed by all sorts of users in AD. These applications all talk to SQL.

    My understanding is that the simplest thing to do is to create a SQL login from the AD group 'Domain Users' and give them the 'Connect' permission. This allows everyone through the 'front SQL door'.

    Let's say that I have a database called 'Sales' and the people that need to access objects in this database belong to the 'Sales' AD group. So the simplest thing would be to add the Sales AD group as a database user. Let's then say I give the 'Sales' database user SELECT and INSERT permissions to the 'allsales' table within the Sales database.

    Is the SQL security logic as follows?

    User Fred is a member of the 'Sales' group and runs an application which does a SELECT on the allsales table.

    SQL sees that Fred is a member of Domain Users which has the connect permission and therefore lets Fred through the 'front SQL door'. SQL then checks that Fred is a member of the Sales group which has Select permission on that table and therefore everything works.

    If the above logic is true, then I cannot understand why the New database user GUI, with User Type, of 'Windows User' has to have a mapping to a login? Does SQL have to have the database user mapped to the login to be able to check the Login Connect permissions? If I don't enter a login and just add the AD group and press OK, the Login becomes the same as the AD group - what is wrong with that?

    Thanks

    David Z

    Tuesday, October 25, 2016 10:02 PM

Answers

  • I've read through this long thread, but I am not sure that it is going anywhere.

    To answer where you started: No, you don't need to add Sales as a login to SQL Server. If you want users to connect to the server through Domain Users but get database access through membership to Sales, that should work just fine:

    CREATE LOGIN [DOMAIN\Domain users} FROM WINDOWS
    go
    USE some_db
    go
    CREATE USER [DOMAIN\Sales]

    Done.

    It seems from the initial post that you were lured by the UI in SSMS, but there is an easy workaround that problem: don't use it.

    • Marked as answer by David Zemdegs Sunday, November 6, 2016 9:27 PM
    Friday, November 4, 2016 5:30 AM

All replies

  • An Windows account that connects to SQL Server is called a Windows Login. And yes, should you choose to add the domain users group to your SQL Server database, any windows login will be able to make a connection to SQL Server.

    You are further correct in that a Windows Group called Sales added as a Windows Login to SQL Server, and further added as a database user or group to the Sales Database will be able to connect to the sales database.

    Granting individual permission to select on that table to the Sales database user group will give them rights to read the allsales table.

    Fred as a member of the sales AD group and the Sales database would be able to select from this table.

    While you can create database users without a login, to answer your question directly you can add a group, but in the new login dialog you need to select Groups, otherwise it will think you are adding a user and search the ad for that user and come up empty.

    • Proposed as answer by Kalman Toth Wednesday, October 26, 2016 9:48 PM
    Tuesday, October 25, 2016 10:21 PM
  • An Windows account that connects to SQL Server is called a Windows Login. And yes, should you choose to add the domain users group to your SQL Server database, any windows login will be able to make a connection to SQL Server.

    You are further correct in that a Windows Group called Sales added as a Windows Login to SQL Server, and further added as a database user or group to the Sales Database will be able to connect to the sales database.


    OK this is what I don't get. I have added Domain Users as a SQL Login. Why do I have to add the Sales group as a SQL login? Why cant I just add the Sales group as a database User?
    Tuesday, October 25, 2016 10:29 PM
  • we are getting involved in semantics here.

    But to answer your question it appears to me that you are trying to add the AD Sales group as a user to SQL Server. Then you are trying to add it as a database user. SQL can't find the user Sales as it is a group.

    When you add the AD group to SQL as a group, you can then add it as a group to the database.

    Here is what it will look like in the database. You can see it is a group there too. I added the local machine's Local Administrators group to SQL Server and then to the database Images. It shows up as a database user.

    HTH

    Tuesday, October 25, 2016 10:34 PM
  • I think I understand your question now. You have added the domain users group Domain Users to SQL Server as a group. You do not understand why also have to add the Sales Group.

    You don't - just add the Domain Users SQL login (technically speaking we call it a windows login), to the Sales database and to the allsales table.

    Just and  FYI, we try to restrict permission on objects according to the principal of least privildge - restrict rights to only those who need it and only grant the minimal rights they need to do their job.


    Tuesday, October 25, 2016 10:38 PM
  • Still not quite there...

    Why do I have to add the Domain Users group SQL login to the Sales database?

    There are two parts to this process - Connecting to SQL and accessing SQL objects.

    Connecting to SQL is done by adding Domain Users as  SQL Login and giving this group Connect permissions. Thats it - this group does not get added to anything else.

    Accessing the SQL objects is done by adding the Sales group to the database and giving it the necessary permissions. I'm not adding the Sales group as a SQL login. Members of that group get the connect permission by virtue of the fact they are also a member of the Domain Users group.

    Which bit about the above have I misunderstood?

    Tuesday, October 25, 2016 10:45 PM
  • There are two parts to it, gaining access to SQL Server. You have thrown the door open wide to all domain users, and SQL Server knows about them by the name MyDomain\DomainUsers (hypothetical name mind you).

    Domain Users can access SQL Server, but really only connect to the master database and very little beyond that.

    Now you have to give this login which SQL Server knows about by the name MyDomain\DomainUsers to the database.

    Now you mentioned a group called Sales. If the Sales Group has rights to the Sales database every member of the Sales AD group will have whatever rights you assign to them in the Sales database. But the Sales AD group must be part of the Windows Login assigned a Login to SQL Server.

    SQL Server knows about inheritance, so it knows that Fred is a member of the Sales AD group, but it does not know that Fred as a member of the domain group Domain users is also part of the Sales AD group. It knows about inheritance but it can't recursively walk all ad groups that the member might be part of.

    However - I am confused - are there 2 groups in this SQL Server - Sales and Domain Users? Is the Sales group added to the Sales database? Fred's membership should flow and he should be able to access the allsales table by inheritance through the Sales group not through the Domain Users group.

    Tuesday, October 25, 2016 11:20 PM
  • I think I have to backup here and do this bit by bit as Im not getting the lightbulb.

    I add a SQL login for the 'Domain Users' group. This is the default AD group that everyone is made a member of. Under 'Securables' for that login is the Server object and the permission 'Connect to SQL'.

    This is the first step. I do this step to make things simple because all AD accounts will need to access the SQL server at some point. The above gives them access to the thorugh the front door but no access to any databases (except for the default 'view' assigned to the public role that this login gets assigned to by default).

    Is the above true? And is the default database irrelevant?

    Do I have to assign this group as a database user? If so, why? I want to control database object access via other AD groups. I am only using the above group for the 'Connect to SQL' permission, not any database object permission.


    Tuesday, October 25, 2016 11:33 PM
  • that is correct - you have thrown the door wide open to everyone who is a member of your domain.

    SQL Server only knows about this entity as a name - BuiltIn\Users I believe. It knows nothing about any group membership this entity's members belong to.

    You can then grant this entity rights to access a database and either grant them access to individual objects in that database, or put them in a role.

    But know you have a problem - people in the Janitors group have access to the sales database and objects you grant it to.

    you have no ability to restrict access because you threw the door open to everyone and SQL Server does not have the ability to know about groups within domain users.

    What you need to do is add the sales group as a Windows group to SQL server - a SQL login in your parlance - but we dbas say a Windows Login. Then you need to make the Sales group a database user to the Sales database.

    Now you can add them to a role, or grant them access to individual objects.

    Tuesday, October 25, 2016 11:50 PM
  • 'SQL Server only knows about this entity as a name - BuiltIn\Users I believe. It knows nothing about any group membership this entity's members belong to.

    You can then grant this entity rights to access a database and either grant them access to individual objects in that database, or put them in a role.'

    This has really confused me. We cannot use AD groups to grant permissions because it knows nothing about group membership?

    Also, I don't want to grant this entity rights to access a database. I just want members of Domain Users to have the 'Connect to SQL' permission. I want to use other AD groups to provide access to database objects.

    Tuesday, October 25, 2016 11:54 PM
  • Maybe I can use a file system analogy?

    I have a folder called corpdata. I give the "domain users' Group read access to 'this object only'. This is the front door. Anyone can see the folders below the corpdata folder. I have a folder called 'Sales' below corpdata. I give the 'Sales' group read access to this folder. I DO NOT give the Domain user group read access to this folder. Therefore only members of the Sales group can read data within the Sales folder.

    I do not have to give the Sales group any permission on the corpdata folder and do not give the domain users group any access the sales folder. 

    Im imagining the SQL security world works the same way but I am obviously wrong. So how do I achieve the same thing in SQL - i.e. everyone through the front door and only certain groups for certain databases?

    Wednesday, October 26, 2016 12:28 AM
  • SQL Server will know about that group. But it will not know that members of that group belong to sales, power users, domain admins, etc. It will just know about Domain Users. It can't walk the hierarchy to see what other groups the domain users are part of.

    You can use whatever group you want and add it as a group to SQL Server, just don't expect SQL to interpolate group membership within that group.

    Domain Users will be added as builtin\users and SQL Server will only know about it.

    Add the Sales group and SQL Server will know about it and you can add this more granularly to databases.

    Wednesday, October 26, 2016 12:39 AM
  • It doesn't work that way. It knows about Domain Users as Builtin\Users it does not know about any group membership individual users in Domain Users may belong to.

    Most dba's like the idea of not allowing global access to SQL Server and instead adding groups who need to access resources within SQL Server and then granularly grant them access to the objects they need to access.

    Wednesday, October 26, 2016 12:42 AM
  • I don't want SQL to interpret subgroups.

    All SQL needs to do is determine whether an account is a member of the Domain Users group so they get the connect to SQL permission. Thats the group I add as a SQL login. Then when they access the database, SQL just needs to know that the account is also a member of the 'Sales' group as I have only added the sales group as a database user.



    Wednesday, October 26, 2016 12:49 AM
  • SQL Server AFAIK does not do that. I believe this is to provide a highly secure RDBMS.
    Wednesday, October 26, 2016 12:52 AM
  • SQL cannot determine who is a member of a group?
    Wednesday, October 26, 2016 8:38 PM
  • SQL can determine if a user belongs to a group if that group is defined as a windows login in SQL Server.

    If a user belongs to a group that is not in sql server SQL doesn't know about that group and hence does not know that the user does not belong to said undefined group.

    if you were to add the AD Sales group to SQL Server and then Add the Sales Group as a database user to the Sales Database SQL Server would discriminate against all domain users who do not belong to the Sales group and not let them into the Sales database. Fred who belongs to the Sales AD group would be able to access resources granted to the Sales database user group in the sales database.

    Most people don't like the unrestricted access to SQL Server through adding the domain users group as a login to SQL Server. They restrict access and grant it on a more granular basis.

    Wednesday, October 26, 2016 8:44 PM
  • So you cannot add an AD group only as a database user, you must add it as a SQL login as well? That's doubling up and doesnt sound like a good design.

    In regards to unrestricted access to SQL server, all I am trying to do is give everyone the 'Connect to SQL' permission. Other groups would then determine their database object access. I have to do that as we will have applications that everyone can run that must log to SQL server.

    Wednesday, October 26, 2016 8:50 PM
  • SQL Server defines a login which gains you access to SQL Server. This can be a windows login or group. You can also have a sql authentication login. The SQL authentication logins cannot be placed in groups within SQL Server.

    So I could not place the SQL Authentication login test into the Sales AD group which you can grant connection access to SQL Server.

    Now once a login (SQL Authentication or Windows login or Group) is authenticated to SQL Server you further need to grant access to this entity as a database user.

    So an AD group like sales or domain user (again I stress this is a bad idea), granted login access to SQL Server must further be granted database access as a database user and access within that database. Once a login is granted access to a database you can grant access granularly to an object (select, update, or delete on a table for example), or add them to a group - db_datareader which means they can read any table in that database.

    This way you can control what a user can access.

    You need to read about SQL injection attacks to discover why SQL Server limits access to all server and database resources and why this is considered to be a good design.

    Wednesday, October 26, 2016 9:04 PM
  • 'SQL Server defines a login which gains you access to SQL Server. This can be a windows login or group.'

    The 'gain access' bit is technically defined as the 'Connect to SQL' permission on the Server instance object yes?

    So therefore I can add the AD group "Domain Users' as a SQL login and make sure it only has the above permission. Therefore any user will be able to connect to SQL. The only other permission they will have is based on the fact that they are assigned the public role which only has the permission to view databases.

    Is the above correct?

    Wednesday, October 26, 2016 9:28 PM
  • "Domain Users" is not a good group name. Choose a better name.

    BOL on public role: "Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.

    Note: public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public."

    https://msdn.microsoft.com/en-us/library/ms188659.aspx?f=255&MSPPError=-2147217396



    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Edited by Kalman Toth Wednesday, October 26, 2016 9:53 PM
    Wednesday, October 26, 2016 9:52 PM
  • '"Domain Users" is not a good group name. Choose a better name.'

    I dont understand. I thought you could add a Windows AD group as a SQL login. 'Domain Users' is an AD group.

    Wednesday, October 26, 2016 9:59 PM
  • Kalman - Domain Users is a default windows group like domain admins.

    David you can add AD and local machine windows groups to logon to SQL server.

    With the Domain Users group, when added to SQL Server it will be added as Domain\Domain Users.

    It is a very very very bad idea to do this, as if any user is compromised his/her account will automatically be able to access SQL Server.

    Secondly once added as a group you have not ability to granularly restrict access based on the group name. For example the Domain Users group login can be assigned to the HR database. You want to make sure that only the HR folks are able to modify the salary table. How do you restrict access to only the HR logins? You can't, unless you add them as a separate group. With the domain users login group being granted access to the HR database and the salary table anyone can modify their own or other's salaries.

    You do not have the ability to apply rights to any users who might be a member of the Domain Users group as SQL Server can't enumerate the Windows groups in the AD. All it knows about is the Domain Users group.

    HTH

    Thursday, October 27, 2016 1:11 AM
  • 'Secondly once added as a group you have not ability to granularly restrict access based on the group name. '

    Why can't I add a different AD group at the database level as a SQL User?

    Thursday, October 27, 2016 3:11 AM
  • Yes, that is the idea, this is the best security practice. But now, the domain users group is serving no purpose - other than to let everyone into your SQL Server, which again is a very bad idea. Makes your life easy, but will bite you in the end.

    Create AD groups for each anticipated role, add them as logins to SQL server, then add them to the database and add them into the roles they need to do their job.

    Thursday, October 27, 2016 3:21 AM
  • If I have Domain Users as a SQL login then why do I have to add other groups as SQL logins? Cant I just add them as a database user?
    Thursday, October 27, 2016 3:24 AM
  • You can, but you you want everybody in your domain to have the priviliges that only those who will be using this perticalar system need?

    There is nothing special with Domain Users, from SQL Server's perspective. Sure, it contains the enture universe (your full domain), but to SQL Server it is just another group. If you add this group as a login, then all your users will be able to login to your SQL Server. If you don't do anything else, then this is pretty meaningless, since you didn't create a user in any database ("map") to the login.

    Now, if you add a second group, and map this to a database. Then those in this second group will be bale to login to SQL Server, from both this second group and from Domain Users. They will get the combined priviliges from the two groups, but none we assigned to Domain Users, so adding that group didn't change anything for those in this second group.

    Bottom line is that you get the combined priviliges from the Windows groups that are added as logins. And there is nothing special with Domain Users (except that it contains all users in the domain).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 1, 2016 10:48 AM
  • Because you have can't limit what that domain users group can access. Again to use my salary table example. If you have your "everyone group" and you want to only grant access to the salary table you can't with your everyone group.

    You have to create a new group, called HR, and then add this group to SQL server and your database. With your security model you either grant your everyone group to your salary table which will be a security nightmare or you start with the principle of least privilege access  and only grant access to SQL Server resource on demand so you can control it better.

    Tuesday, November 1, 2016 11:13 AM
  • I need to understand how SQL differs from the security model on ACLs.

    Look at my folder example earlier in this thread.

    Why can't I add Domain Users as a SQL login and give it only 'Connect to SQL' permission, then add, say, a Sales AD group as a database user and give it the necessary permission on the Sales table? I can do this sort of thing with ACLs. 

    Tuesday, November 1, 2016 11:21 PM
  • You are free to add any group you want to SQL Server. It will have the right to connect to SQL server.

    SQL Server will know about this group via a sid.

    query select * from sys.server_principals to see this sid.
    I get the following
    select name, type_desc, sid from sys.server_principals where name like '%domain_users%'

    MyDomain\Domain Users    WINDOWS_GROUP    0x010500000000000515000000A3C9B778A9B98AF8AFF191CE01020000

    The long hex number is how SQL Server interprets a group. Windows resolves windows logins to that sid. SQL does not know about any group membership and individual windows login who belongs to the Domain Users group belongs to.

    All it knows about is that sid.

    You can add that group to any database and it is now a database user which is mapped to that sid. you have no level of granularity with this sid, now database user. Everyone in the domain users group will be able to access any database you give it access to. Any role membership you give the domain users to, every domain user will be able to access that role.

    If you grant access to a salary table everyone can give themselves a raise.

    If you want to restrict usage to another group, or user they have to be added to SQL Server in that group or as that user. Now SQL Server will have a new sid that they can use to add to a database and grant rights to individual objects.

    To repeat - this architecture allows administrators to grant access to SQL Server objects on a restricted use policy.

    To repeat - making your life easier by adding the entire domain users group to SQL Server is a security risk and does nothing for you as you will now need to add individual users or groups in addition to the domain users group to access privileged resources.

    If you have a problem with this design choice you will find it on all other RDBMs I have worked on, so I can't really refer you to a RDBMs which will have a model more in line with your relaxed security policy which will at some point in time not work out in your best interests not to mention the company you work for.

    Tuesday, November 1, 2016 11:57 PM
  • I know all about SIDs. I still don't see the difference between the ACL security model and the SQL security model. Please see my folder example earlier. To repeat, I can give 'Domain Users' permissions on the parent folder AND NOT on the child folder. I can give the 'Sales' group permission on the Sales child folder AND NOT on the parent folder. This allows members of the Sales group access to the sales folder only.

    Why is adding 'Domain Users' as a SQL login and giving it only 'connect to SQL' permissions a security risk. In AD, Authenticed Users is added by default with the read permission and Microsoft dont see this as a risk.

    Wednesday, November 2, 2016 12:22 AM
  • All SQL knows about is the sid. It can't extrapolate group membership beyond that sid. I understand your example - but SQL does not have a mechanism to interpret what groups or logins which exist in Windows belong to that sid. So, to use the earlier example with the Windows Sales group, SQL knows about the sid for domain users, but knows nothing about the Sales group until it is added to SQL Server as a group login. Now you will be administering the Sales group when granting it to other databases or objects within the database.

    So, it is different from your folder metaphor. But I think in your folder metaphor - Domain users has read access to your root folder, but then a new group - Sales has rights to the child folders. I am not sure if there is inheritance here from the root folder. I am wondering if it is the same mechanism in SQL.

    NTFS is a file system which is very much like a database in some respects - which is why it was going to be a database in winfs.

    Now, adding domain users is a security risk as someone may be lax in their personal security and may be hacked. now they have access to SQL Server. At some point in time a hack may exist where they could do damage in SQL Server even when they can only login. We close the door to everyone but those who have a need to logon to SQL Server and do useful work there.

    Kind of like the security I have in my home. The whole world might at some point in time need a key to access my home and do useful work, but I don't distribute my house key to everyone. I keep my bedroom door locked, but I feel a whole lot less safe knowing that everyone in the world has my house key, even if I have a very good lock on my bedroom door which I have only handed out to a few people;)

    HTH

    Wednesday, November 2, 2016 12:42 AM
  • I still don't understand. I dont need to extrapolate from the sid.

    ACLs have inheritance but that's not what my example shows. It shows that at the high level everyone can see the Sales folder but at the Sales folder level, only Sales group members can access anything below that. The model is very secure and I cannot see how SQL security can be so flakey when the ACL model is so solid.

    "SQL knows about the sid for domain users, but knows nothing about the Sales group until it is added to SQL Server as a group login."

    This is something I asked earlier in this thread - Can I add an account as a database user without adding it as a SQL login?

    Wednesday, November 2, 2016 1:05 AM
  • "Can I add an account as a database user without adding it as a SQL login?"

    No.

    Does ACLs have inheritance? I know it looks like they do, but perhaps what they do is apply the sids to all children which exists in the root. Now you want to add the Sales windows group to a child folder. You add it, and now you have a new sid added to the child in addition to what is on the root.

    running cacls

    c:\temp PUBLISHER\hilary:(OI)(CI)F
            Everyone:(OI)(CI)R
            BUILTIN\Administrators:(OI)(CI)F
            NT AUTHORITY\SYSTEM:(OI)(CI)F

    C:\Temp>cacls c:\temp\subdirectory

    c:\temp\subdirectory PUBLISHER\hilary:(OI)(CI)F
                         PUBLISHER\Sales Team:(OI)(CI)R       
                         Everyone:(OI)(CI)R
                         BUILTIN\Administrators:(OI)(CI)F
                         NT AUTHORITY\SYSTEM:(OI)(CI)F

    see, sales team is added separately which is akin to a new group being added as a login to SQL Server.

    Wednesday, November 2, 2016 1:19 AM
  • "Can I add an account as a database user without adding it as a SQL login?"

    No.

    If I knew that earlier it would be much clearer :-)

    To me that appears to be a bad design. If I want to control access to database objects I have to add the AD account twice, once as a SQL login and again as a database user. If that's what I have to do then so be it.

    But I don't see the security problem with adding 'Domain Users' just as a SQL login and not adding it to any databases. Remember that early on in my thread I said:

    "all AD accounts will need to access the SQL server at some point"

    If you had an application that EVERYONE ran that needed to log information to a SQL server, how would you set up the security?

    Wednesday, November 2, 2016 2:51 AM
  • " If I want to control access to database objects I have to add the AD account twice, once as a SQL login and again as a database user."

    Yes. You first add the login, be it an SQL login, A Windows user or a Windows group. then you add that login as a user to each database that it is supposed to have access to. This is how SQL Server work, quite simply.

    "But I don't see the security problem with adding 'Domain Users' just as a SQL login and not adding it to any databases."

    If you don't add it as a user to any database and don't grant it any server level priviliges, then there is probably no security risk. BUT, then it is entirely meaningless to add it in the first place. 

    "If you had an application that EVERYONE ran that needed to log information to a SQL server, how would you set up the security?"

    If you really mean everybody in the organization, then you could use Domain Users. And then of course add this login as a user to the database(s) required and grant priviliges to that user in the database(s).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 2, 2016 11:52 AM
  • Still not addressing the issue of everyone in the front door and restricted users for database objects.

    So finally I've learnt that the silly SQL model is that I have to add  group twice, both as a SQL login and database user. So I add 'Domain Users' as a SQL login and give it 'connect to SQL' permissions. Then I add 'Sales' as a SQL login because I have to then add 'Sales' as a database user and give it the necessary Sales database object permissions.

    I'm looking for the detailed logic here. This is how Im guessing it goes:

    I run an app and it tries to access SQL. The very first check that SQL does is look at the SIDs in my access token and sees if one of those SIDS has the 'Connect to SQL' permission. Is this correct so far?

    Thursday, November 3, 2016 9:02 PM
  • That is correct - you are authenticated by your sid through group membership or your windows login. Once logged on when you attempt to access further resources (databases and database objects) it will be decided by your sid.
    Thursday, November 3, 2016 9:41 PM
  • I need to step through this slowly as hopefully that will help nail the issue.

    so the first check is the 'Connect to SQL' permission. As the 'Domain Users' group SID is in my access token and assuming the 'Domain Users' group is added as a SQL login and granted the 'Connect to SQL' permission then that is why I pass the first step.

    Next lets assume that the app does a Select on the sales table in the sales database. Im guessing SQL will now check if I have the Select permission on that object. Lets say I am a member of the group Sales and that the Sales group has been added as a database user with the select permission. Then because the Sales group SID is also in my access token, then SQL will say OK?

    Friday, November 4, 2016 12:59 AM
  • What you mean is you Create a Login to SQL Server. This could mean a Windows Login, a Windows Group Login, or a SQL Authentication Login.

    Here is where I think you are having your disconnect. I granted access to the Windows group - domain users - you would login as DavidZemdegs and get access to sql server.

    SQL Server would know you as DavidZemdegs but your would be the sid of the domain users group. It knows you as DavidZemdegs because that is your Windows account you authenticated with. SQL uses the domain to authenticate you and then looks and sees what groups you belong to and what groups have access to SQL Server.

    Now if you are a member of multiple groups SQL Server will still know you by your login (davidZemdegs) but will also know that you belong to a multiude of windows groups which are defined as logins to SQL server - we call them on entity in SQL Server - server principals.

    So let's go through your example and consider 2 options. The login DavidZemdegs only has membership in the Domain Users group. The domain users group has been granted access to SQL Server. Your account is also a member of the Windows Sales group. SQL Server knows nothing about this server principal  as it is not in SQL Server.

    Lets assume this app impersonates your account. It connects to SQL server but the sid for the Domain Users group does not have access to the sales database or the sales table. David is very unhappy:(

    But in your example now SQL Server does know about the Windows Domain Sales group as it is granted access to SQL server, and the sales group has access to the Sales database and the Sales table.

    You logon and your Windows account DavidZemdegs now is mapped to two sids - one for the domain users group and one for the sales group.  When you try to access the sales database only your membership in the sales group gets access and that sid is used to determine further access in the sales database.

    So in your description - yes, you are exactly right. When authenticating your login will carry around all sids belonging to all groups you belong to in SQL server as well as your own sid. When trying to access resources all will be tried until one works. 

    HTH

    Friday, November 4, 2016 1:54 AM
  • I like to step through bit by bit as your information confuses the issue even more im afraid.

    In the Windows world I am my access token i.e. just a bunch of SIDS. Windows doesnt care whether these are group or user SIDS. So therefore, if 'Domain Users' has been granted 'Connect to SQL' permission, the fact that Ive logged in as davidzemdegs is irrelevant as its the SID of the Domain Users group that is allowing me to connect to SQL?

    Friday, November 4, 2016 2:02 AM
  • When you logon and do this select suser_name() you get DavidZemdegs. SQL knows you as that windows account.

    Internally it must map you to a server_principal
    select * from sys.server_principals

    But DavidZemdegs does not exist there. The Group DomainName\Domain Users does. And that will be the sid used to give you access to database users. If the Windows Sales group was added to SQL server and you were a member of the Sales group and you logged on as DavidZemdegs (you must logon with a windows account (ignoring SQL Authentication now)), now SQL Server will check both sids to see what they can access in SQL Server.

    For example if the domain users were in the db_denydatareader role, and the sales group was in the db_datareader role, the deny data reader role would deny you any access to the sales table. Here is an example of both sids coming into play to determine what rights you have on the sales table.

    HTH

    Friday, November 4, 2016 2:26 AM
  • I get what you are saying but its the terminology that is confusing. I need to concentrate on the actual SQL permissions and what they allow me to do.

    I add 'Domain Users' as a SQL login. It only has the permission 'Connect to SQL'. When I log on (doesnt matter who I am), my access token has 'Domain Users' and therefore I have the 'Connect to SQL' permission.

    You mentioned that 'The Group DomainName\Domain Users does. And that will be the sid used to give you access to database users'.

    Its my understanding that the 'Connect to SQL' permission does not 'give you access to database users'. 

    Friday, November 4, 2016 2:33 AM
  • I've read through this long thread, but I am not sure that it is going anywhere.

    To answer where you started: No, you don't need to add Sales as a login to SQL Server. If you want users to connect to the server through Domain Users but get database access through membership to Sales, that should work just fine:

    CREATE LOGIN [DOMAIN\Domain users} FROM WINDOWS
    go
    USE some_db
    go
    CREATE USER [DOMAIN\Sales]

    Done.

    It seems from the initial post that you were lured by the UI in SSMS, but there is an easy workaround that problem: don't use it.

    • Marked as answer by David Zemdegs Sunday, November 6, 2016 9:27 PM
    Friday, November 4, 2016 5:30 AM
  • Thanks - that would be the easiest and preferred option but I have been told by others that I cannot add the Sales group as a database user unless I add it as a SQL login as well. So I'm not sure what to believe!
    Sunday, November 6, 2016 8:57 PM
  • Erland is correct. I just tried it out. I was unware of this.
    Sunday, November 6, 2016 9:04 PM
  • Thanks - I'm glad the answer is so simple and that SQL works like the ACL model.


    Sunday, November 6, 2016 9:33 PM