blocking access to tables

[Paulvtis]

I would like to 'deny' access to certain users in sql but the problem is i have around 500 tables. for each tables i have numerous columns. does sql have a mass update feature rather than i have to deny access for example the 'delete' access for each table?

if not, can i execute an sql script to simply automate the 'deny' to all 'delete' access for all tables?

tnx

 

[JamesLean]

You can use the fixed database role db_denydatawriter - this denies INSERT, UPDATE and DELETE permissions on all tables to the role members.

If you only want to deny DELETE permissions to all tables, I would create a new role, use Ent Mgr to deny delete permissions on all tables and then add users to this role.

--James

 

[Paulvtis]

do i have to deny one by one for each table on the particular role? what if i have 1000 tables do i have to click 1000 times?

can't i run a script indicating for a certain role that i want to deny the 1000 tables rather than deny it one by one?

tnx

 

[JamesLean]

Unfortunately, you have to specify each table separately, either in a TSQL script or clicking them all thru Ent Mgr.

--James

 

[Paulvtis]

tnx to all of you

 

[Paulvtis]

by the way under the object properties then permissions you can put permissions related to:
select
insert
update
delete
exec
DRI

what is DRI and where is this used?

tnx

 

[JamesLean]

DRI is referential integrity permissions.

Say you have Table1 which User1 does not have any permissions on. He creates Table2 but needs to add a foreign key to it which references Table1. You can give him DRI permissions on Table1 so he can create the FK but still not have any other permissions that would allow him to, say, actually select from the table.

--James

 

[Paulvtis]

tnx to all of you

 

[mihaildamjan]

As mentioned before you should vreate a group for which you would deny delete.

That could be done in a loop sot hat you do not have to go manually through all the 500 tables

This is the procedure I created some time ago for that
Good luck

--******************************
declare @i as int , @Tab as varchar (2000) , @Proc as varchar (2000) , @sql as varchar (2000)
select @tab = min (name) from sysobjects where xtype = 'u'
set @i = 1
while @i < 2000
BEGIN
set @sql = 'deny delete on '+@tab+' to WhatEverGroupName '
--select @tab
exec (@sql)
select @Tab = min(name) from sysobjects where xtype = 'U' and name > @tab
if @tab is null set @i = 2000
END
--*************************