Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
I remembered Deny overriding everything, but I'm still not getting the desired behavior. My user2 still cannot make updates to the dbo.customers table, even thought they are a member of app_customers. I'm expecting SQL Server to aggregate the AD Groups, but it doesn't appear to be working. Am I going about this the wrong way? I'm wondering if I should be using roles instead? –  jj. Dec 1 '11 at 20:18
    
Can you run the code above and post the results? –  mrdenny Dec 1 '11 at 21:26
    
I figured it out. I was running these tests by setting the permissions directly against the AD groups in sql server. As soon as I created roles (with those permissions) and mapped them to the ad groups I started getting the behavior I desired: anyone in group app_customers now has access to dbo.customers, and anyone not in that group does not. –  jj. Dec 1 '11 at 22:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.