Re: How to script security changes for all tables but a few



How is the user getting access to the 3000 tables?

I know you are sometimes constrained by a previous configuration that you
can't change, but if you can change the environment I would:
Create a role for most people, such as CREATE ROLE GeneralAccess
Add most users to that role but do not add the user that you wish to
exclude.
Grant permissions on the schema to the GeneralAccess role.
Create a role for the restricted access user, such as CREATE ROLE
LimitedAccess
Add the restricted access user to that role.
The grant permissions on just the two tables to the LimitedAccess role.

The general principle is that you are granting access to a few things,
instead of denying access to lots of things. At one point you have the
public role in your code below. Public should never have access to anything
that isn't really, really public.
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Nate" <n_t_schultz@xxxxxxxxxxx> wrote in message
news:a5b2ea1b-12da-471d-850f-3d1266bda888@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am having some difficulty finding a way to script deny for select,
alter, update, delete, insert for 3000+ tables. I would simply deny
access to the schema, but there are two tables in this schema the user
must have access to.

Here is what I have come up with but I can't figure out how this
should actually be scripted.

I am sure I am not the first to ask this, but after searching for two
days I couldn't find anytyhing.

declare @count as numeric
set @count = 0

declare c1 cursor for\

declare @file2 as fulltable
select table_schema +'.'+ table_name as fulltable
from information_schema.tables
where table_type ='Base Table' and table_schema='qadta'
and TABLE_SCHEMA+'.'+TABLE_NAME NOT IN
('testing.FA132910','testing1.FA12011')


*
loop thru all selected records and
change permissions

*/

while @@fetch_status = 0
begin
set @count = @count + 1
set @file2 = @file --Not sue about this
EXEC ('GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
' + @file2 + ' TO [public]')

fetch next from c1
into @file

end

print 'Files processed ' + str(@count)

close c1
deallocate c1


)


.