revoke delete permission from large no of tables

Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2
D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:

> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>
|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2

revoke delete permission from large no of tables

Hi,
I have a database with 500+ tables and 1000+ views and equal no of
stored procedures. I have a sql server login id which I have included
in the following roles:
db_datareader
db_datawriter
now the issues is.. i want to revoke delete permission from certain
tables. Any revoke or deny do not affect the behaviour i.e. even after
successfully executing revoke/deny statements on these tables for
DELETE statement.. user is able to DELETE.
pl advise the best way to revoke delete permission from a few (half
dozen) tables out of the total.
regards,
D2D2,
Well, I think that REVOKE will not help you much, because still the login is
part of the db_datawriter role, but DENY should.
How is that login deleting the rows, using a stored procedure created by a
user with greater rights?
AMB
"D2" wrote:
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2
>|||> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
As Alejandro mentioned, DENY takes precedence over granted permissions so if
a DENY DELETE should override the DELETE granted by db_datawriter
membership. Note that a REVOKE will remove a previous GRANT or DENY so if
you first execute a DENY and then a REVOKE, the DENY is lost.
Also, keep in mind that ownership chaining overrides permissions when
objects are accessed indirectly, If the user has execute permissions on a
stored procedure, permissions on indirectly used objects are not needed as
long as the objects involved have the same owner.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"D2" <dhapola@.yahoo.com> wrote in message
news:9f959c94-c6d0-4f9e-8098-0b51ce7de26a@.e6g2000prf.googlegroups.com...
> Hi,
> I have a database with 500+ tables and 1000+ views and equal no of
> stored procedures. I have a sql server login id which I have included
> in the following roles:
> db_datareader
> db_datawriter
> now the issues is.. i want to revoke delete permission from certain
> tables. Any revoke or deny do not affect the behaviour i.e. even after
> successfully executing revoke/deny statements on these tables for
> DELETE statement.. user is able to DELETE.
> pl advise the best way to revoke delete permission from a few (half
> dozen) tables out of the total.
> regards,
> D2