Re: DENY ALL for all tables

From: Cindy Gross (cgross@online.microsoft.com)
Date: 08/15/02 

From: cgross@online.microsoft.com (Cindy Gross (MS))
Date: Thu, 15 Aug 2002 01:02:24 GMT

If you don't want your users to be able to read from all tables in the databaes, then don't add them to the db_datareader role. Instead, grant select to the tables
you do want them to have access to.

Example:

use tempdb
exec sp_addlogin new
exec sp_adduser new
exec sp_addrole sqlreaders
grant create table to new
go
setuser 'new'
go
create table table1 (col1 int not null)
go
setuser
go
grant select on new.table1 to SQLREADERS
exec sp_helprotect 'new.table1'
go
drop table new.table1
sp_dropuser new
sp_droprole sqlreaders
sp_droplogin new

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Relevant Pages

  • Re: Permission to Insert records for DB USERS
    ... To add to the response by Vyas, consider creating your own database ... EXEC sp_addrole 'Supervisors' ... GRANT SELECT ON MyTable TO Supervisors ... > I am a fresh developer for the SQL server and have no Idea> for the permissions on the database. ...
    (microsoft.public.sqlserver.security)
  • Re: grant execution permission
    ... execute permission to the user/role: ... EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser' ... GRANT EXEC ON MyProcedure TO MyRole ...
    (microsoft.public.sqlserver.security)
  • SMS SP2 db upgrade errors
    ... NULL exec sp_addrole 'smsschm_users' DECLARE @pwd varcharSET ... OPEN newviews FETCH NEXT FROM newviews INTO @viewname WHILE ... SELECT ON Report TO webreport_approle GRANT SELECT ON ... DrillThroughColumns TO webreport_approle GRANT SELECT ON Dashboard ...
    (microsoft.public.sms.admin)
  • Re: Login account scripts
    ... EXEC sp_addlogin 'User', 'Password', 'DefaultDB' ... To allow permissions on tables, you can use fixed roles, ... also grant permissions on objects with the grant statement ... >in the default database? ...
    (microsoft.public.sqlserver.security)