Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: User in instance vs database

    I'm database novice and would like to get confirmation or correction on my understanding of the difference between a user under a sql server instance vs a user in a sql server database. For example, a user can be added under Instance-->Security--Login and a user can be added under Instance-->Databases-->Database Name-->Security-->Users.

    Is the difference that the login under the instance is used for logging into the database via management studio and the user under the database is for database access? Also, is one dependent on the other? So if I want to add a user to the database, they have to be added to the instance first?

    Thank you

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A login has access to the server.

    A user has access to a database.

    Users are (usually, but not always) mapped to logins.

    A login can only be mapped to one user in one database, but can be mapped to users in multiple databases.

    Separating logins from users allows you to give a user access to a database he is working with, and refuse him access to the payroll database that is sitting in the same instance.

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    Thank you for the response. So a user can be mapped to a login. Can the user and login be the same windows domain account using windows authentication?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The login would be mapped to a windows domain account. A user can not directly be mapped to a windows domain account.

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    Thanks again, so the user can be a random account and only resides in the database?

    Here's the scenario, I have a group of domain users that need read access to a database using windows authentication on a 3rd party software on the front end, not management studio. I have a domain group created with all the users in it. So if I understand correctly, I need to create a new login with the domain group account and then create a random user in the database and map it to the login?
    Also, can the user be the same name as the login? Can it be a domain account?
    Last edited by shobuddy; 11-28-12 at 16:09.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The names can be the same all the way through, but you can not deny access for management studio (or MS Access, or Excel, etc.), without getting into a bit of coding. Even then, it is hit or miss. Access to a database (really any server resource) is either granted or not, there is no conditional bit on what programs the user can or can not use.

  7. #7
    Join Date
    Mar 2010
    Posts
    7
    Thanks, that clears quite a few things up for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •