April 19, 2010 at 2:47 am
Hello!
I am having an issue using custom SQL Server 2005 roles for an ASP.NET application I am creating. I want to create custom roles in my DB and use those roles to grant users access to data and stored procs. Basically, I want to use them like AD groups (ie. grant users connect access to the database, then add them to the appropriate DB role(s) for data access).
I have tried several times to make this work, but I get differing results.
1) When I 'Deny' execute permissions for a role on a stored proc and execute my ASP.NET app under my account, I am still able to execute it and get back the values, even though the roles I am assigned to are explicitly denied execute permissions on that stored proc.
2) I have also created a test AD account and given it connect access to my db and assigned it to two custom roles I created. It seems that the deny execute permission work with it. However, if either one of the two roles that the account is assigned to is denied execute access to my stored proc, that overrides the grant execute permission given to the other role. Is there a way to fix that?
Thanks,
Kris
April 19, 2010 at 3:38 am
I have noticed some unusual things can happen when you use DENY permissions.
I think in most cases, a DENY will take priority over any implicit GRANTs - but there are some circumstances where an explicit GRANT will take priority.
Its worth researching this in Books Online (BOL) as its a lot more complex than it seems.
April 21, 2010 at 1:17 pm
deny overrides grant except in your case becasue you are in the sysadmin role.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply