Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 20 Aug 2006 11:06:17 -0500
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?
Personally, I use GRANTs almost exclusively and avoid explicit DENY except
in special cases. IMHO, GRANTs are easier to understand and manage.
It's unclear to me why you explicitly denied execute on UpdateResearch to
the Research group in your example. If you were to revoke this deny
permission from Research, it seems to me you would achieve the desired
result; Bob could not execute the proc because he is a member of only
Research yet Dan could because of his IT role membership. Is there another
scenario in your environment that requires the deny?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<d_lepre@xxxxxxxxxxx> wrote in message
news:1155912277.053570.244930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.
.
- Follow-Ups:
- References:
- Prev by Date: Re: How to drop all objects with permissions for a role
- Next by Date: Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- Previous by thread: Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- Next by thread: Re: SQL 2000 Windows Authentication - Same User Multiple Groups
- Index(es):
Relevant Pages
|
|
