Overlapping Permissions

I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:

>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:

> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:

> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:

> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:

> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>