SQL 2005 AD Group permission levels

Question

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?

Answer 1

Deny will always override grant. You need to revoke the DENY right to app_users. If the user doesn't have rights you don't need to DENY rights unless you never want them to have rights to the table.

select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
    sys.database_permissions.permission_name,
    sys.database_permissions.state permission_state,
    sys.database_permissions.state_desc,
    state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
    sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
    sys.database_principals.principal_id
order by 1, 2, 3, 5