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
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
from information_schema.tables
where table_type ='Base Table' and table_schema='qadta'

loop thru all selected records and
change permissions


while @@fetch_status = 0
set @count = @count + 1
set @file2 = @file --Not sue about this
' + @file2 + ' TO [public]')

fetch next from c1
into @file


print 'Files processed ' + str(@count)

close c1
deallocate c1



Relevant Pages

  • Re: Re: Re: SQLXML Namespaces
    ... > namespace, so SQLXML has declared an arbitrary prefix for the ... > the results you can declare any prefix you like and the XML ... >> Your schema must have a default or target namespace ...
  • Re: dealing with schema name - how to manage?
    ... Always use the schema prefix instead of relying on implicit schema? ... Google found me script to modifyed stored procedures to dbo here: ... DECLARE @FromSchema varchar ...
  • Re: XSLT 2.0 space separated list type access with Saxon 9.1 -sa -val
    ... I think using the data function gives you the typed value so doing e.g. ... Regarding the type I tried to declare the type bt the "as" attribute. ... Do I have to import the schema? ... XPST0051: SequenceType syntax error at char 0 in: ...
  • Re: dealing with schema name - how to manage?
    ... Why not just change the default schema for you on the 2005 server to u1088043_sa2; for the user you are working as? ... Tibor Karaszi, SQL Server MVP ... So, right now, I still stuck with having to reference the objects with a user prefix (they are sql 2000 systems). ... DECLARE @FromSchema varchar ...