Re: How to script security changes for all tables but a few
- From: "Rick Byham, MSFT" <rickbyh@xxxxxxxxxxxxx>
- Date: Fri, 21 Aug 2009 09:35:44 -0700
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
Grant permissions on the schema to the GeneralAccess role.
Create a role for the restricted access user, such as CREATE ROLE
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
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
where table_type ='Base Table' and table_schema='qadta'
and TABLE_SCHEMA+'.'+TABLE_NAME NOT IN
loop thru all selected records and
while @@fetch_status = 0
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
print 'Files processed ' + str(@count)
- Prev by Date: Re: How to script security changes for all tables but a few
- Next by Date: Re: Ignorance is frustrating. SSEE win authd user; deny view, etc
- Previous by thread: Re: How to script security changes for all tables but a few
- Next by thread: Re: How to script security changes for all tables but a few