I'm trying to give permissions to a (sql 2005) database app based on AD groups. The general idea is to require a user to have a membership to "app_users" to view anything, and membership to other groups gives them write access to that group. "app_customers" gives write access to the customers module, "app_sales" to sales, etc.
I've listed an example below:
user1: AD member of app_users
user2: AD member of app_users, app_customers
For dbo.customers table:
app_users
- Granted: Select permission
- Denied: Insert, Update, Delete
app_customers
- Granted: Select permission
- Granted: Insert, Update, Delete
I would expect user1 to be able to view the dbo.customers table, but will not be allowed to modify anything (insert/update/delete) - which works. In the same vein, I would expect user2 to be able to view AND modify the dbo.customers table, since they are a member of app_customers.
However, this is not the case. Instead, user2 is denied any modifications just like user1. I seem to remember something about deny permissions winning if there was a conflict, but it's honestly been too long since I've dealt with them.
Am I going about this the right way?
Thanks for your time!
