I recently investigated an issue where a domain user was "suddenly" unable to perform select queries on any table in a database. This was after verifying that the user had the correct permissions from within Management Studio under Security -> Logins. The most apparent evidence that it was a permissions issue is that when the user was given the sysadmin role, he regains the ability to do his queries. Of course, granting the sysadmin role is not the proper solution.
It turns out that recently, the users were migrated to a new Active Directory group. This new group was given the correct permissions in the database, while the old group was "deactivated" by adding the db_denydatareader and db_denydatawriter role memberships. Unfortunately for this user, he was added to the new group, but not removed from the old group, and since db_denydatareader takes precedence over db_datareader, the poor user was locked out of the database. So the lesson is, aside from checking to make sure that the user has the correct permissions, make sure that all login groups that the user is a member of also has the correct permissions. It only takes one db_denydatareader role to override all the db_datareader roles granted.
No comments:
Post a Comment