Re: SQL 2000 Windows Authentication - Same User Multiple Groups

Well, that blows my idea apart :) Whatever you do, it sounds like there are
going to be code changes in your future. I would have suggested app. roles,
but since you've already ruled that out... Anything I could think up
without looking at your code and configuration would probably involve
re-writing a lot of your code... Sorry

<d_lepre@xxxxxxxxxxx> wrote in message
news:1156253553.446172.72410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
One database, one set of stored procedures.


Mike C# wrote:
One question - are you talking about two different databases and two
different sets of stored procedures? Or one database and one set of
stored
procs?

<d_lepre@xxxxxxxxxxx> wrote in message
news:1156172021.086641.93640@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dan,

Thanks for the response. Here's the real issue ... perhaps my example
confused things but I hope this clears things up.

- We have a user ("User1") that is in two different Windows user
groups: "WinGroup1" and "WinGroup2".

- We have two applications that interface with the same SQL Server
database ... one app is an internally developed VB .NET WinForm app via
ADO.NET ("App1") and the other one is a 3rd party app via ODBC
("App2"). Ideally, both apps should connect via Integrated security
(we don't want to manage individual users).

- User1 is a member of WinGroup1 and WinGroup2, and he is also a user
of App1 and App2.

- App1 contains CRUD functionality using stored procs (EXEC perms on
all stored procs for WinGroup1).

- App2 contains some CRUD functionality using stored procs but we want
to ensure only the read-only stored procs can execute (EXEC perms on
only SELECT stored procs for WinGroup2).

How can we configure our database to ensure that User1 can perform CRUD
functionality using our WinForm app and read-only functionality using
our 3rd party app?

Thanks,

- Dan


Dan Guzman wrote:
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.





.

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: 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: cant block delete permissions
    ... If you have any roles apart from db_datareader and db_datawriter revoke ... Now execute the delete statatement on that table. ... opened the table's 'manage permissions' dialog and explicitly denied delete ... We looked at her database role membership and saw that she was a member of ...
    (microsoft.public.sqlserver.security)
  • Re: Application execution?
    ... The first place would be to check permissions of the folder for the ... a computer as a regular user and then use runas to execute filemon/regmon ... of aministrator group can execute my .net application, ... member of users or power users .net apllication can't execute. ...
    (microsoft.public.win2000.security)
  • How do I execute xp_sendmail on a shared SQL Server ?
    ... I dont have permissions on the 'master' DB. ... Any Ideas on how I can execute extented stored procs in ...
    (microsoft.public.sqlserver.security)