Post reply

Do user accounts have to exist in SQL Server, if they're going to be part of database roles?

  • April 21, 2017 at 11:56 am

    #329904

    I'm working with a SQL Server 2008 R2 developer database. We're working on an app. We've got to make it so that if users are in certain database roles, then various things are permitted, or not, for them being in those roles. Yesterday I found that the users we're working with (2 in this case) both had accounts under Security | Users and they also were in the database role. I might be naïve about this, but I thought that if they were merely in the role, that would be sufficient. So I deleted them from Security | Users. Now when I go to add another, test user to the same database role, I can't see either of the previous two.

    So have I messed things up by deleting them from Security | Users in SSMS? Do I have to put them back?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • April 21, 2017 at 12:57 pm

    #1939377

    Yes you need the Users entries.  Long story short, for 2008 R2:
    -  Logins are at the instance level, and can be an active directory user, an active directory group, or a SQL Authenticated login
    -  Users are at the database level, and are associated to a specific instance login
    -  Roles are at the database level, and a database user can be a member of the role.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply