locked
Question about SQL server login vs database user RRS feed

  • Question

  • I have been using MS SQL server for several years and have been able to accomplish the things on the job that require SQL server.    However, I still do not truly understand the SQL server login vs database user.  I can give the proper definition of both terms, but that doesn't mean a really understand it.  A SQL server login allows access to the server and the database login allows access and gives permissions to a specific database.  I know that a user is mapped to a login.  But again, I don't see the it.  The light bulb is not on. When I need to setup a piece of client software that requires me to put in server, instance, and authentication information, I select SQL server authentication, and enter the login information for the sysadmin and it just works.  I never enter anything about a database user.

    Can someone please explain this to me so that I can understand it?  Please don't point me to a Microsoft msdn or technet article.  I need something in laymans terms.  Just a couple of statements or maybe a paragraph.  thanks

    Friday, March 1, 2013 9:57 PM

Answers

  • A sysadmin possesses the magical and godlike power to connect to any database as dbo.

    There are really two different ways you can access a resource. 

    1) because you have been granted permission

    2) because you own it

    Owners don't need permissions.  A database is owned by the Login mapped to dbo.  An instance is owned by it's sysadmins.  A Windows instance is owned by its administrators.  And when you own a thing, you cannot be excluded from any part of it.  There is no way to keep a Windows Admin from gaining access to a SQL Server instance.  And there is no way to keep a sysadmin from gaining access to a Database.

    Any non-sysadmin login would need to be mapped to a database user to access a database.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Friday, March 1, 2013 10:24 PM
  • Since you are doing everything as sysadmin, you did not come across the situations liks 'permission denied on the database'. Sysadmin is highest previleged user and you can do anything with it on your database. you certainly do not want unauthorized people to acess the database server with that login.
    database user permissions gives the rights to the user to waht he/she can do inside the database.there are several roles  within the database, you can assign the user to a role or you can also customize as them permissions on certain tables.

    Example :
    if you want to give UserA  only "Select permission" on all your tables, you can give db_datareader role on the database.
    if you want to give userB "Select" permission on TableA but not TableB, you can explicitly grant it.

    also, you should not let the application connect as  SA user and other major bad thing, any body who can open the config file, can see the SA password and also, this password will be sent over the network, there is some serious security loopholes in your method.

    Also, you do not need enter anything about database user. as you said, every user should be mapped to a login and when you connect to the database, your permissions are checked behind the scenes, if you are login is 'sysadmin', you have the right to do anything and there is no need to map the user to a database and all that stuff.
    but , if you login is regular login, you credentials are evaluated against the database you are trying to work on based on your role/permissions, you will be able to do stuff within that database. 


    Hope it Helps!!







    • Edited by Stan210 Friday, March 1, 2013 10:40 PM
    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Friday, March 1, 2013 10:18 PM
  • My version. Think about the reverse situation; who do you want to keep out? Presume two people: Joe and Sue. Both use this instance of SQL Server so they both have a login. But you only want Joe to have access to the Payroll database, and you only want Sue to have access to the Planning database. So you need a second barrier; a gateway at the database level for each database. That's the database user account. 

    Summary:
    Logins: Joe and Sue
    Payroll database users: Joe
    Planning database users: Sue

    Permissions that affect the whole instance of SQL Server (ALTER ANY LOGIN or CREATE SERVER ROLE), can only be granted to logins (server principals).
    Permissions that affect a single database (SELECT ON Table1 or CREATE DATABASE ROLE), can only be granted to database users (database principals).

    The difference between the login and the user can be hard to see, because logins and database users frequently have the same name. Though they don't have to. (You can map a database user named PayrollUser to a login named Joe. And create a database user named PlanningUser to the same login Joe as long as that user is in a different database.)

    A common situation is a login for a Windows group (such as SQLUsers). But then having individual users (Joe or Sue) as the database users. Then they get server access as the SQLUser login, but database access as Joe or Sue.

    Note: I have simplified a bit, overlooking other server and database principals such as role.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:43 PM
    Friday, March 1, 2013 11:26 PM
  • Regarding "if this login is used by everyone wouldn't they all get the same access to the databases?" Remember that logins don't have database access. Users have database access. All logins would get the same access to the SQL Server instance. If that group login is mapped to one a database user, then they will all get the same database access. But you don't have to retain the grouping. Once they have access to the SQL Server through the group login, you can (if you wish) grant database access individually.

    Your scenario is sensible.

    Your sa password is being kept by the application. So only your application developer will know where it is kept. (Which I realize isn't much help.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 6:21 PM
  • Windows User: Joe
    Windows User: Fred
    Windows User: Sally

    Windows Group: SQLUsers
    Members of SQLUsers in Active Directory: Joe, Sally

    SQL Server Windows Authentication login: SQLUsers (This is what I meant as a group login. It's a SQL Server Windows Authentication login that represents a Window group.)

    Who can connect to SQL Server? Joe- yes, Sally - yes, Fred - no.

    In Database Accounting, the login SQLUsers is mapped to a database user named SQLUsers.
    Who can use database Accounting: Joe and Sally.

    In Database Planning, the login SQLUsers does not have a mapping to any database user.
    In Database Planning, you can create a database user named Joe. Joe does not have a login under the name "Joe." But when Joe connects as the Windows user Joe, his access token contains the SID (security identifier) of all his Windows groups; which includes the SID of SQLUsers. So Joe passes the first authentication step, and is granted access to SQL Server. Note that SQL Server knows it is Joe; that is, SQL Server doesn't think he is "SQLUser." SQL Server knows a Windows group when it sees one.
    When Joe attempts to change to the Planning database, SQL Server sees that SQLUsers doesn't have a user in that database, but SQL Server can see that Joe has a user mapped under his own name, so he get's in.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 9:22 PM
  • I do not want to confuse but I think in the second case, Rick was mentioning about a case where you “create a database user without a login”.

    Example : you have a windows group called 'DEV'.  SQL server has login for that windows group 'DEV'. JOE is a windows user but  JOE does not explicity have a login with sql server, however JOE is part of 'DEV' Group

    so, JOE can connect to sql server.

    Let's say JOE needs to connect to a database 'TEST'. Only JOE needs to connect but no other members of 'DEV' group should be able to. In that case, you create a database user without login.

    use TEST

    GO

    Create user Domain\JOE WITHOUT Login

    GO

    sp_addrolemember 'db_datareader','DOMAIN\JOE'

    Now, JOE can connect sql server because he is part of the DEV group and he can connect to Test database because he had explicit permission on the database. As rick said, sql server knows the connection came from JOE, even though it authenticates as JOE as 'DEV' on the server level.

    This is my understanding on the concept behind...


    Hope it Helps!!


    • Edited by Stan210 Monday, March 4, 2013 10:32 PM
    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 10:27 PM

All replies

  • Since you are doing everything as sysadmin, you did not come across the situations liks 'permission denied on the database'. Sysadmin is highest previleged user and you can do anything with it on your database. you certainly do not want unauthorized people to acess the database server with that login.
    database user permissions gives the rights to the user to waht he/she can do inside the database.there are several roles  within the database, you can assign the user to a role or you can also customize as them permissions on certain tables.

    Example :
    if you want to give UserA  only "Select permission" on all your tables, you can give db_datareader role on the database.
    if you want to give userB "Select" permission on TableA but not TableB, you can explicitly grant it.

    also, you should not let the application connect as  SA user and other major bad thing, any body who can open the config file, can see the SA password and also, this password will be sent over the network, there is some serious security loopholes in your method.

    Also, you do not need enter anything about database user. as you said, every user should be mapped to a login and when you connect to the database, your permissions are checked behind the scenes, if you are login is 'sysadmin', you have the right to do anything and there is no need to map the user to a database and all that stuff.
    but , if you login is regular login, you credentials are evaluated against the database you are trying to work on based on your role/permissions, you will be able to do stuff within that database. 


    Hope it Helps!!







    • Edited by Stan210 Friday, March 1, 2013 10:40 PM
    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Friday, March 1, 2013 10:18 PM
  • A sysadmin possesses the magical and godlike power to connect to any database as dbo.

    There are really two different ways you can access a resource. 

    1) because you have been granted permission

    2) because you own it

    Owners don't need permissions.  A database is owned by the Login mapped to dbo.  An instance is owned by it's sysadmins.  A Windows instance is owned by its administrators.  And when you own a thing, you cannot be excluded from any part of it.  There is no way to keep a Windows Admin from gaining access to a SQL Server instance.  And there is no way to keep a sysadmin from gaining access to a Database.

    Any non-sysadmin login would need to be mapped to a database user to access a database.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Friday, March 1, 2013 10:24 PM
  • Thanks to both of you for the replies.  This definitely helps.  I need to read these post several more times to fully understand what is being said, especially Stan's as it goes into lot's of detail.  I will re-post Monday with followup questions.  Please check back Monday.  thanks
    Friday, March 1, 2013 10:35 PM
  • I think David has said it in a very nice way..once you understand a little bit about logins/users/permissions, it makes all sense..

    Hope it Helps!!


    • Edited by Stan210 Friday, March 1, 2013 10:38 PM
    Friday, March 1, 2013 10:37 PM
  • My version. Think about the reverse situation; who do you want to keep out? Presume two people: Joe and Sue. Both use this instance of SQL Server so they both have a login. But you only want Joe to have access to the Payroll database, and you only want Sue to have access to the Planning database. So you need a second barrier; a gateway at the database level for each database. That's the database user account. 

    Summary:
    Logins: Joe and Sue
    Payroll database users: Joe
    Planning database users: Sue

    Permissions that affect the whole instance of SQL Server (ALTER ANY LOGIN or CREATE SERVER ROLE), can only be granted to logins (server principals).
    Permissions that affect a single database (SELECT ON Table1 or CREATE DATABASE ROLE), can only be granted to database users (database principals).

    The difference between the login and the user can be hard to see, because logins and database users frequently have the same name. Though they don't have to. (You can map a database user named PayrollUser to a login named Joe. And create a database user named PlanningUser to the same login Joe as long as that user is in a different database.)

    A common situation is a login for a Windows group (such as SQLUsers). But then having individual users (Joe or Sue) as the database users. Then they get server access as the SQLUser login, but database access as Joe or Sue.

    Note: I have simplified a bit, overlooking other server and database principals such as role.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:43 PM
    Friday, March 1, 2013 11:26 PM
  • I think the light bulb has just came on.  After reading all the above post I finally see how it works.

    The only thing I need a little clarification on is the last thing Rick says:
    "A common situation is a login for a Windows group (such as SQLUsers). But then having individual users (Joe or Sue) as the database users. Then they get server access as the SQLUser login, but database access as Joe or Sue."
      In this case what login would the SQLUsers group use, one that I create?  Also, if this login is used by everyone wouldn't they all get the same access to the databases?  I need this statement clarified.  thanks - tom

    One last thing:  This all deals with an accounting application we are using.  The application logs in using the SA login.  As you all have said SA can do anything in any database and I should not be doing it this way.  So basically I need to create another login for example  "User-X" and map that to a database user that I can also call "User-X", then set permissions on the database for that user.  Does this sound correct?  Did I leave out anything?


    Also, what configuration file has my sa password in it?  I can't find a file with my sa password in it.
    • Edited by Poly Admin Monday, March 4, 2013 5:52 PM
    Monday, March 4, 2013 5:03 PM
  • Regarding "if this login is used by everyone wouldn't they all get the same access to the databases?" Remember that logins don't have database access. Users have database access. All logins would get the same access to the SQL Server instance. If that group login is mapped to one a database user, then they will all get the same database access. But you don't have to retain the grouping. Once they have access to the SQL Server through the group login, you can (if you wish) grant database access individually.

    Your scenario is sensible.

    Your sa password is being kept by the application. So only your application developer will know where it is kept. (Which I realize isn't much help.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 6:21 PM
  • Thanks Rick -

    Please give an example as I am confused by: All logins would get the same access to the SQL Server instance. If that group login is mapped to one a database user, then they will all get the same database access. But you don't have to retain the grouping. Once they have access to the SQL Server through the group login, you can (if you wish) grant database access individually. 

    What exactly are you referring to as a "group login". 

    I understand that logins don't give access to databases but logins are how you "login".  You don't login using the database user.  The database user doesn't even have a password.       thanks again

    Monday, March 4, 2013 7:44 PM
  • Windows User: Joe
    Windows User: Fred
    Windows User: Sally

    Windows Group: SQLUsers
    Members of SQLUsers in Active Directory: Joe, Sally

    SQL Server Windows Authentication login: SQLUsers (This is what I meant as a group login. It's a SQL Server Windows Authentication login that represents a Window group.)

    Who can connect to SQL Server? Joe- yes, Sally - yes, Fred - no.

    In Database Accounting, the login SQLUsers is mapped to a database user named SQLUsers.
    Who can use database Accounting: Joe and Sally.

    In Database Planning, the login SQLUsers does not have a mapping to any database user.
    In Database Planning, you can create a database user named Joe. Joe does not have a login under the name "Joe." But when Joe connects as the Windows user Joe, his access token contains the SID (security identifier) of all his Windows groups; which includes the SID of SQLUsers. So Joe passes the first authentication step, and is granted access to SQL Server. Note that SQL Server knows it is Joe; that is, SQL Server doesn't think he is "SQLUser." SQL Server knows a Windows group when it sees one.
    When Joe attempts to change to the Planning database, SQL Server sees that SQLUsers doesn't have a user in that database, but SQL Server can see that Joe has a user mapped under his own name, so he get's in.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 9:22 PM
  • Ok, my mind was stuck on SQL authentication.  I wasn't thinking about Windows authentication.

    You obviously know your stuff.  But when you say "create a database user named Joe" that does not have a login named Joe, he has to be mapped back to some SQL Login correct?  Otherwise you can't create the database user.
    • Edited by Poly Admin Monday, March 4, 2013 10:20 PM
    Monday, March 4, 2013 9:59 PM
  • I do not want to confuse but I think in the second case, Rick was mentioning about a case where you “create a database user without a login”.

    Example : you have a windows group called 'DEV'.  SQL server has login for that windows group 'DEV'. JOE is a windows user but  JOE does not explicity have a login with sql server, however JOE is part of 'DEV' Group

    so, JOE can connect to sql server.

    Let's say JOE needs to connect to a database 'TEST'. Only JOE needs to connect but no other members of 'DEV' group should be able to. In that case, you create a database user without login.

    use TEST

    GO

    Create user Domain\JOE WITHOUT Login

    GO

    sp_addrolemember 'db_datareader','DOMAIN\JOE'

    Now, JOE can connect sql server because he is part of the DEV group and he can connect to Test database because he had explicit permission on the database. As rick said, sql server knows the connection came from JOE, even though it authenticates as JOE as 'DEV' on the server level.

    This is my understanding on the concept behind...


    Hope it Helps!!


    • Edited by Stan210 Monday, March 4, 2013 10:32 PM
    • Marked as answer by Poly Admin Tuesday, March 5, 2013 1:44 PM
    Monday, March 4, 2013 10:27 PM
  • Ok.  I am using the gui and saw no way to create a database user without mapping it back to a login.  Now that I know you can create a database user (by referencing a user from the domain) without having to map it back to a login it makes more sense now.

    Is there a way to do it in the gui?
    • Edited by Poly Admin Monday, March 4, 2013 10:46 PM
    Monday, March 4, 2013 10:37 PM
  • you can do it using ssms 2012..It does not look like older SSMS versions had it..not sure

    Also, the user does  not need to be domain user.. you can create a database user 'XYZ' (XZY is not a proper login. login/user does not exists) and give the domain user (Domain\JOE) permissions to impersonate the database user XYZ when connecting to the database.. refer this http://msdn.microsoft.com/en-us/library/ms173463(v=sql.100).aspx .....


    Hope it Helps!!




    • Edited by Stan210 Monday, March 4, 2013 10:58 PM
    Monday, March 4, 2013 10:51 PM
  • Man that was painful but I finally have a decent understanding of how sql logins and database users work.  Thanks for all the helpful posts.  If someone else is confused about logins vs database users they can read this and it should clear it up for them.  thanks again

    • Edited by Poly Admin Tuesday, March 5, 2013 2:36 PM
    Tuesday, March 5, 2013 1:43 PM
  • By the way, I tried to run the command:

    use TEST-DB1
    GO
    Create user domain\tom WITHOUT Login
    GO
    sp_addrolemember 'db_datareader','domain\tom'

    but it gave an error on "domain\tom".  It did not like the "\". 

    Also - When I try to create a database user(with a login mapping) by using a domain user it does not let me.  It gives an error due to the "\" in domain\user.  What am I doing wrong?





    • Edited by Poly Admin Tuesday, March 5, 2013 5:58 PM
    Tuesday, March 5, 2013 5:22 PM
  • Should be something like:

    use [TEST-DB1]

    create login [domain\tom] from windows

    create user [domain\tom] for login [domain\tom];

    grant select to [domain\tom];

    The "grant select" will grant the user SELECT privileges on the whole database.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 5, 2013 6:17 PM
  • I will try this command.

    This can't be done from the gui?

    From the gui I was trying to create a database user and map it back to a Windows group login that I had already created, but it didn't like the "\" in domain\tom.

    UPDATE - this command did not work for me.  It errors on the "\".
    I simply cannot create a database user from a windows domain account, but I don't know why.  And it's driving me nuts!!

    • Edited by Poly Admin Tuesday, March 5, 2013 7:18 PM
    Tuesday, March 5, 2013 7:10 PM
  • The syntax I posted is correct.  You must use the [] around the domain\account.  Post the exact command and error.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 5, 2013 7:35 PM
  • This is exactly what I entered and poly\sql-users is a group login that already exists.

    Use [test-db1]
    Create user [poly\jpeaden] for login [poly\sql-users]
    grant select to [poly\jpeaden]


    Error:

    msg 15006, Level 16, State 1, Line2
    'poly\jpeaden' is not a valid name because it contains invalid characters.

    Tuesday, March 5, 2013 7:48 PM
  • I do not think you can map a individual user to group login..that's why you are getting the error... if you want to give permissions to poly\jpeaden user only..

    do 

    Use [test-db1]

    GO

    create user  [poly\jpeaden] without login

    GO

    grant select to [poly\jpeaden]

    jpeaden will be authenticated at the server as member of the group and the group has login and jpeaden has  explicit login on the database, so, he can access the database.

    if you want to map the group to database users,you can create a new user(non-domain) or can map it to the same login
    Example:

    Create USER groupusers for login  [Domain\sql-users]

    or

    Create USER [Domain\sql-users] for login [Domain\sql-users]


    Hope it Helps!!



    • Edited by Stan210 Tuesday, March 5, 2013 8:07 PM
    Tuesday, March 5, 2013 7:57 PM
  • That command completes successfully but when I look at the properties for the new database user it shows the login name as poly\jpeaden.  I thought it was supposed to be without login????

    That's it!!  I appreciate all the help from everyone but I am tired of trying to figure this craip out.  I am gonna stick to using one database user that maps to one login for each person or application that needs access to a database.  That will keep it simple.  Does anyone see any problems with handling it that way?


    • Edited by Poly Admin Tuesday, March 5, 2013 8:12 PM
    Tuesday, March 5, 2013 8:08 PM
  • not really..however, you can get more flexibility and as easy maintenance, if you can map the logins to one user,for the same permissions....

    yes, I think it does it behind the scenes to map to that user to login so that databases knows which user is mapped to the login. but under your logins, you will  not really see this login.


    Hope it Helps!!

    Tuesday, March 5, 2013 8:16 PM
  • > I am gonna stick to using one database user that maps to one login for each person or application that needs access to a database.  That will keep it simple.  Does anyone see any problems with handling it that way?

    That will work fine.  You can add a Login for a group and a database user for the group to allow group access. eg

    use test
    create login [builtin\users] from windows;
    create user [builtin\users] for login [builtin\users];

    SQL 2012 adds the ability to create a database user without in individual login, where the user is either authenticated directly at the database or has a group login: SQL 2012 CREATE USER

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 5, 2013 8:53 PM
  • I see.  Yes, you don't see the login under logins.

    Did you mean map multiple users to one login?  I don't understand the benefit of mapping multiple logins to one user seeing as how the user is tied to a specific database.

    Tuesday, March 5, 2013 9:04 PM
  • I should have ended my explanation earlier. I think I added to much. You probably should stick to having users mapped to logins. However, since we are in this deep:

    A "USER WITHOUT LOGIN" is a different kind of beast. It's a receptacle for permissions, but no one can connect to SQL Server using such a login. It does not apply in this instance.

    A single login (such as your [poly\sql-users] ) can only be mapped in a database to a single user, and it will include all of the members of [poly\sql-users], not just[poly\jpeaden].

    What you are trying to do (I think) is this: 
    CREATE LOGIN [poly\sql-users] FOR WINDOWS LOGIN [poly\sql-users];
    USE DATABASE dbname;
    CREATE USER [poly\jpeaden] FOR WINDOWS LOGIN [poly\jpeaden];

    This should leave you with a login for a Windows group. And a user for a Window user that is a member of that Windows group. (Yes, the syntax is confusing since it says FROM WINDOWS LOGIN [poly\jpeaden] and there isn't such a login [it's more of an implicit login].) Since you have a user without an obvious tie to a login, it can confuse people later when they look at your structure. If the login is later dropped, the database user account will remain, but without a login they might not have any way to connect to SQL Server.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, March 5, 2013 9:26 PM
  • thanks for everyone's help with this confusing (to me) issue.  I certainly understand it much better now.
    Tuesday, March 5, 2013 10:06 PM
  • Thanks Rick for clarifying on this. one more little clarification...if we specify "windows\user" name to create the user without login, i.e (Create user [Poly\Jpeaden] without login)
    if that user exists as "login or as a member of a group that has login on sql server", that user will be created with login of that user.

    In other words, Create user [Poly\Jpeaden] without login or create user [Poly\JPeaden] for login [Poly\Jpeaden] has same effect in this case.
    since the poly\Jpeaden is member of sql-users and he is authenticated at server level and even though I said

    Create user [Poly\Jpeaden] without login

     it still assigned him to the login 'poly\jpeaden' on the database as that login implicty exists at the server as part of [poly\sql-users].

    I understand it has been long thread and probably confusing to the Originial Poster and myself but it sure is worth the learning. Thank you very much.


    Hope it Helps!!



    • Edited by Stan210 Tuesday, March 5, 2013 10:47 PM
    Tuesday, March 5, 2013 10:41 PM
  • SQL 2012 adds the ability to create a database user without in individual login, where the user is either authenticated directly at the database or has a group login:SQL 2012 CREATE USER <http://msdn.microsoft.com/en-us/library/ms173463.aspx>

    It is worth pointing out that for this to be possible the database must be a contained database, which is a new kind of database mode in SQL 2012.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, March 5, 2013 10:50 PM
  • Database authentication is a contained database feature, but adding a user who has a group login works in any SQL 2012 database.

    EG:

    create login [builtin\users] from Windows
    
    create user [dbrowne0\alice]

    Will create a database user who logs in using a group login.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 6, 2013 3:35 PM
  • I think I grok this sufficiently (SQL 2012 and contained database questions aside for now) , but let me try to add this:

    Domain user: MyDomain\Mark   is a member of Domain_Users and SQLTestUsers

    in SQL Server:

    Create USER TesterGroup for login  [MyDomain\SQLTestUsers]

    allows members of domain group SQLTestUsers to attach or login to the SQL Server instance itself.

    DB-A in the SQL Server instance then has:

    create user  [MyDomain\Mark] without login  <- lets me into the database container itself

    sp_addrolemember 'db_datareader','MyDomain\Mark' <- gives me effective rights inside the database to tables & data.

    Subsequently, if MyDomain\Mark is removed from the SQLTestUsers group in Active Directly, the account still technically has database rights, but can't get there because the account has no permissions to "talk to" the SQL Server instance (presuming he's not part of another group that has access to the server instance). 

    To make that real-world applicable now, in DB Admin terms, this allows a "fixed" set of DB permissions to be promulgated across DEV, TEST, and PROD SQL Server instances unchanged at the database level, but users may or may not be able to gain access to the particular server instance depending upon NETWORK group memberships assignments.

    Does that help?


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)




    Thursday, March 7, 2013 9:40 PM