locked
Does every login require a user? RRS feed

  • Question

  • We have a group of people who edit a set of tables directly on the database.

    So of course they want separate logins for themselves -- basically I create their logins with similar permissions by

    1. create a role with such permissions

    2. create a login per person who needs it

    3. create a user per login

    4. add that user as a member of the role created on #1

     

    Is this the best practice?

    I can't seem to find any best-practice-related-article on this, only articles on creating roles/users/logins, etc.

     

    Thank you for your help!

    Friday, July 16, 2010 3:19 AM

Answers

  • Q: Does every login require a user?

    A: By default, the database includes a guest user when a database is created. Permissions granted to the guest user are inherited by logins who do not have a user account in the database.

    Guest user can be disabled however it can not be dropped on any database.

    Loginless user:

    It is possible to create a user in the database that is not associated to a login, referred to as a loginless user.

    Loginless users are designed to replace application roles. Loginless users also provide a much better audit trail than an application role because each user must authenticate to the instance using their own credentials instead of using a generic account.

    SQL Server security works on the principle of “no access by default.” If you haven’t explicitly been granted permission, you cannot perform an action. You grant access to a database by adding a login to the database as a user by executing the CREATE USER command

    The SID of the login is mapped to the database user to provide an access path after a user has authenticated to the instance. When a user changes context to a database, SQL Server looks up the SID for the login and if the SID has been added to the database, the user
    is allowed to access the database. However, just because a user can access a database, that does not mean that any objects within the database can be accessed since the user still needs permissions granted to database object(s).

    I think you are using SQL Server login and creating individual login for each user. However it is advised to use Window domain Login / Group

    Using Windows groups provides the greatest fl exibility for managing security access. You simply add or remove accounts from the group to control access to a SQL Server instance. A DBA is also isolated from the details of people joining and leaving companies or moving to
    different groups within an organization. The DBA can then focus on defi ning groups based on permission profi les and leave the mechanics of adding and removing user accounts to standard business processes within your company.

    Using Windows groups provides the greatest flexibility for managing security access. You simply add or remove accounts from the group to control access to a SQL Server instance. A DBA is also isolated from the details of people joining and leaving companies or moving to
    different groups within an organization. The DBA can then focus on defi ning groups based on permission profi les and leave the mechanics of adding and removing user accounts to standard business processes within your company.

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:26 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 3:59 AM
    Friday, July 16, 2010 6:58 AM
  • In addition to Sivaprasad's comments.

    It is generally speaking a best practice to leave 'guest' disabled.  If you enable it, you need to be sure you know why you decided to do that instead of granting the rights specifically.   The downside of 'guest' is that logins that do not need access will get it automatically, opening up your database to unexpected uses. 

    If you use Windows Groups to provide access to the database, you can still track which individual login is in use at any time.  Also, if you like to stamp updates with the login name, that will provide the underlying login, not just the group name.  E.g.

    UPDATE MyTable SET ...  Last_Username = SUSER_SNAME()  WHERE Key_Value = <some key value>

    FWIW,
    RLF

     

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:27 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 3:59 AM
    Monday, July 19, 2010 3:27 PM

All replies

  • Q: Does every login require a user?

    A: By default, the database includes a guest user when a database is created. Permissions granted to the guest user are inherited by logins who do not have a user account in the database.

    Guest user can be disabled however it can not be dropped on any database.

    Loginless user:

    It is possible to create a user in the database that is not associated to a login, referred to as a loginless user.

    Loginless users are designed to replace application roles. Loginless users also provide a much better audit trail than an application role because each user must authenticate to the instance using their own credentials instead of using a generic account.

    SQL Server security works on the principle of “no access by default.” If you haven’t explicitly been granted permission, you cannot perform an action. You grant access to a database by adding a login to the database as a user by executing the CREATE USER command

    The SID of the login is mapped to the database user to provide an access path after a user has authenticated to the instance. When a user changes context to a database, SQL Server looks up the SID for the login and if the SID has been added to the database, the user
    is allowed to access the database. However, just because a user can access a database, that does not mean that any objects within the database can be accessed since the user still needs permissions granted to database object(s).

    I think you are using SQL Server login and creating individual login for each user. However it is advised to use Window domain Login / Group

    Using Windows groups provides the greatest fl exibility for managing security access. You simply add or remove accounts from the group to control access to a SQL Server instance. A DBA is also isolated from the details of people joining and leaving companies or moving to
    different groups within an organization. The DBA can then focus on defi ning groups based on permission profi les and leave the mechanics of adding and removing user accounts to standard business processes within your company.

    Using Windows groups provides the greatest flexibility for managing security access. You simply add or remove accounts from the group to control access to a SQL Server instance. A DBA is also isolated from the details of people joining and leaving companies or moving to
    different groups within an organization. The DBA can then focus on defi ning groups based on permission profi les and leave the mechanics of adding and removing user accounts to standard business processes within your company.

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:26 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 3:59 AM
    Friday, July 16, 2010 6:58 AM
  • In addition to Sivaprasad's comments.

    It is generally speaking a best practice to leave 'guest' disabled.  If you enable it, you need to be sure you know why you decided to do that instead of granting the rights specifically.   The downside of 'guest' is that logins that do not need access will get it automatically, opening up your database to unexpected uses. 

    If you use Windows Groups to provide access to the database, you can still track which individual login is in use at any time.  Also, if you like to stamp updates with the login name, that will provide the underlying login, not just the group name.  E.g.

    UPDATE MyTable SET ...  Last_Username = SUSER_SNAME()  WHERE Key_Value = <some key value>

    FWIW,
    RLF

     

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:27 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 3:59 AM
    Monday, July 19, 2010 3:27 PM