Am I being role happy?

I'm working on the security portion of this vb app. In the application the
user is presented with a list of commands that can be executed on a piece of
data.

My client wants to be able to block certain users from seeing certain
commands on their screen.

The commands are loaded into a listview from a table.

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

Thanks in advance.
J
P.S. The application has just under 500 users and is going to hit 1000 in a
couple of years.Me (me@.here.com) writes:
> I've created a role for each combination of command/province(A user may be
> able to execute a command on an Ontario piece of data but not a Quebec
> piece).
> I just modified the query that loads the list box so that it includes an
> IS_Member with the command/province combination.
> The roles were easy to create(sql query to create the sp_addrole
> commands).
> And the query modifications were easy as well. Is_Member barely affects
> query times(very fast).
> I've also created roles that contain these roles so a user only has to be
> added to one role to get access to large groups of commands.
> I may have one problem though. I've created hundreds of roles on the
> server. Is that a problem? Can anyone tell me if there's a better way of
> doing this?

The one thing that I don't really like this design is that you use
SQL Server's own mechanisms to control data access within the
application. I would prefer to have my own authorization tables.
This design can confuse a DBA who thinks roles are for control access
to tables and T-SQL commands.

Then again, if it ain't broke...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp