Re: DENY ALL for all tables

From: Andrew J. Kelly (akelly@targitinteractive.com)
Date: 08/07/02 

From: "Andrew J. Kelly" <akelly@targitinteractive.com>
Date: Wed, 7 Aug 2002 10:19:49 -0400

To the best of my knowledge the default is NO permissions. You have to
place them in that role for them to have those permissions. 

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.
"kevin" <kenglert@nacg.ca> wrote in message
news:403a01c23e12$e54c1fe0$95e62ecf@tkmsftngxs02...
> I've created a new group called [SQLREADERS].  This group
> needs read access to some tables but not all.  The default
> for SQL is to allow new users/groups read access to all
> tables for the role db_datareader.  I need to remove
> access to all tables and then give access back for only
> the tables I want the new group to be able to read.  Using
> Enterprise Manager (and clicking through one table at a
> time) would take a while for over 2000 tables.
>
> I thought that using DENY ALL TO [SQLREADERS] would work
> but it only works on tables belonging to the dbo owner.
> The only way that I can get it to work is by specifying
> one table at a time like DENY ON owner.tablename TO
> [SQLREADERS].
> >-----Original Message-----
> >Why are you trying to DENY all of them?   How did they
> get access in the
> >first place?
> >
> >--
> >Andrew J. Kelly   SQL MVP
> >Targitinteractive, Inc.
> >
> >
> >
> >"Kevin" <kenglert@nacg.ca> wrote in message
> >news:3e3f01c23d84$27495550$95e62ecf@tkmsftngxs02...
> >> I'd like to use the DENY ALL command so that all tables
> >> are restricted for a database.  The problem is that most
> >> tables have a different owner than DBO.  DENY ALL works
> >> great for dbo.customers but new.customers (where dbo and
> >> new are owners).  Is there an easy way to issue the DENY
> >> ALL command for all tables no matter who the owner is?
> >
> >
> >.
> >

Relevant Pages

  • Re: Control over creation of procs & views owned by dbo
    ... To add on to Jasper's response, you could also change object ownership to ... 'dbo' with sp_changeobjectowner. ... security context of the invoking user, not the object owner. ... need permissions on only directly referenced objects. ...
    (microsoft.public.sqlserver.security)
  • Re: revoke change permissions?
    ... admin denys Owner Creator Change Permissions. ... User needs admin help with permissions. ... Owner Creator Deny. ...
    (microsoft.public.windows.server.security)
  • Re: Is There any Resource for SQL Managment
    ... The owner by default has permission to do all of this but ... that have the proper permissions granted, ... have 3 different schema's and assign dbo rights appropriately. ... Now we want to classified the database ...
    (microsoft.public.sqlserver.programming)
  • Re: How to prevent DELETEs in a table
    ... What you say about dbo is true, but it is even MORE true about someone who ... is in the sysadmin role. ... No permissions are ever checked for someone in the ... the deny is not applied. ...
    (microsoft.public.sqlserver.server)
  • Table record editing permissions
    ... The 'dbo' account means that 'sa' is the owner. ... without being granted permissions. ...
    (microsoft.public.sqlserver.security)