I Cannot Get The SQL Server 2005 Roles, Schemas, Users Correctly Setup For Proper Access

I have been struggling with this for a while and cannot get it right. I have read countless articles on the internet as well as MSDN documentation about SQL Server 2005 and no success so far.

What I want is a database with multiple SCHEMAS, DATABASE ROLES and USERS in order to have a finer grade of security to access the various objects.

My schemas are (in order to simplify the situation and provide an example of the setup):

- [dbo] the standard DBO schema which is always default. Contains public objects.
- [com] a special module integrated into the system
- [ofc] contains objects used by back-office only
- [aud] contains objects used for auditing, etc.

Each of these schemas has their fair share of tables, views, functions and stored procedures which have been created appropriately (i.e. CREATE TABLE [ofc].[Addresses]), in other words prefixed by the name of the schema to which they belong.

Then I have created various database roles (don't confuse them with SQL2005 application roles) as follows:

- PublicRole mostly used for viewing, no data alterations
- WorkerRole used by front end processes that need write access to data in [dbo] and [aud]
- OfficeRole used by back-office for dealing with backoffice data (basically [ofc], [aud] stuff)
- AdminRole used by application administrator

Each of these database roles has been defined with owner 'dbo', none of them own any schemas. And last but not least to each of these roles I have selected the above named schemas (dbo, ofc, aud) as securables and for each of those securables schemas I have then given the correct set of GRANT/DENY on the Alter, Control, Delete, Execute, Insert, References, Select, Update, Take ownership and View definition.

As an example, the PublicRole role has been given the following permissions (Y=GRANT, N=DENY):

Table #1 of Application Permissions [dbo] [com] [ofc] [aud]Alter N N N N
Control N N N N Delete N N N N Execute Y Y N Y Insert N N N Y References Y Y N Y Select Y Y N Y Update N N N Y Take ownership N N N N View definition N N N N

And I have the following minimum set of database users defined (the server login has the same name):

- upublic, assigned to PublicRole
- uworker, assigned to WorkerRole
- uoffice, assigned to OfficeRole
- uadmin, assigned to AdminRole

As I understand when I assign these users to a particular custom Database Role, the users inherit the permissions granted to THAT role thus liberating me from having to assign the permissions to each and every user account on the same role.

What I expected was that when I logged in to the database with the upublic user account I would at least be able to view (SELECT at least) ALL the objects with the exception of those in th e[ofc] schema. Unfortunately the account is not able to access ANYTHING at all, I get an error like

"The SELECT permission has been denied on object XYZ, database DB, schema 'dbo'"

So, when I use Management Studio to look at the properties I selected the PublicRole and it showed the upublic user as a member of the role (Members of this role). So far so good.

Then when I switch to the Securables page for this role it shows all the schemas I defined and for each of them the same list shown in Table #1 except it has two lines for each permission, the first for Grantor dbo and the second for Grantor upublic. The first has the same permissions I assigned to the role (as shown on Table #1) but the 2nd does not show a checkmark on either GRANT or DENY!!! For example ([x] = checked, [ ] unchecked):

Table #2 Explicit permissions for Securable
Permission Grantor Grant Grant-with Deny
Select dbo [x] [ ] [ ] Select upublic [ ] [ ] [ ]

What am I doing wrong here? Apparently I then have to repeat the whole grant/deny for EACH and EVERY USER I define when the idea was that I would grant/deny on the database role and every member of that role would inherit those permissions automatically.