SQL 2000 Windows Authentication - Same User Multiple Groups

We have a SQL 2000 server with multiple databases. We currently manage
security at the user level but we're trying to clean things up and move
to using integrated Windows groups.

My question is: what is the best practice for assigning stored proc and
view-level permissions such that we can permit/deny a database action
on the same database object for a user that belongs to more than one
Windows group?

For example ... we have a Research database that is used by 2 groups of
Windows users: Research and IT. Bob is a member of Research but not
IT. Dan is a member of both Research and IT. There are 2 stored
procedures in Research: SelectResearch (performs a select against 1
table) and UpdateResearch (performs an update to 1 table) ... we have
assigned Execute permission on SelectResearch to Research and IT and
Execute permission on UpdateResearch to only IT (and explicitly Denied
to Research).

Due to his group membership, Bob will be able to execute SelectResearch
but not UpdateResearch ... easy enough because Bob is only in one
Windows group and we have assigned the appropriate group permissions on
each of the procs.

We want to allow Dan to execute both the SelectResearch and
UpdateResearch stored procs. However, because we have denied the
Resarch group (of which he is also a member), he won't be able to
execute UpdateResearch.

Due to the "additive" nature of SQL integrated/Windows permissioning,
is there a workaround to the "same user in different groups with
different object permissions" issue?

FYI - we looked into Application roles but they would involve code
changes and we understand that there are connection pooling and other
ADO issues that might cause problems with some of our legacy (COM+/VB6)
applications.

.

Relevant Pages

  • Re: SQL 2000 Windows Authentication - Same User Multiple Groups
    ... App1 contains CRUD functionality using stored procs (EXEC perms on ... UpdateResearch stored procs. ... execute UpdateResearch. ... view-level permissions such that we can permit/deny a database action ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Windows Authentication - Same User Multiple Groups
    ... UpdateResearch stored procs. ... Resarch group (of which he is also a member), ... It's unclear to me why you explicitly denied execute on UpdateResearch to ... view-level permissions such that we can permit/deny a database action ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Effective Permissions Error with Domain User
    ... I set the database compatibility to 2005. ... server profile trace and found that it was calling the Execute As User. ... This leads me to believe it is some sort of permissions issue. ... Did you get these database from SQL Server 2000 by using a RESTORE command? ...
    (microsoft.public.sqlserver.security)