Re: How Can I Create A "Reader" Role?

Doug (nospam@xxxxxxxxxxx) writes:
Giving data reader on SS 2005 also appears to give the privilege of "view
definition" - something I try to stay away from.

If you have SELECT permisson on an object implicitly also gives you
VIEW DEFINITION. You need to explicitly DENY this permission to avoid
this.

Much prefer creating a db role, then generating appropriate grant
statements through a select statement joining sys.objects and
sys.schemas (if I remember correctly) and adding the db user to the new
role.

No need for joining. If you want a role that has SELECT permission on
all objects, but VIEW DEFINITION on none, you can do:

GRANT SELECT TO yourrole
DENY VIEW DEFINITION TO yourrole

However, beware that DENY takes precedence of GRANT, so if you want users
to be able to see the definition of some tables, the above will not be good.

Also beware that several client APIs has methods which reads metadata
under the cover, these methods will stop working if you deny VIEW
DEFINITION.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Column Level Permissions Security Issue
    ... role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY ... When working with security in SQL Server it's imperative to understand ... discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html. ... there are users who have permission to access this ...
    (microsoft.public.sqlserver.security)
  • Re: distribution clean up job fail
    ... The Administrator Account or whatever account the distribution agent is ... running under in sql 2005, or whatever account SQL Server agent is running ... permission on the share and underlying files and folders for this to work. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • RE: Run a ssis package on SQL Server 2005 Error
    ... I can run the SSIS package succesfully in BIDS under the same user as SQL ... The user is a domin user and it has full permission on the remote server, ... SQL Server 2005 and Agent are run under Domin\xxxxx, ...
    (microsoft.public.sqlserver.dts)
  • RE: Keeping User From Deleting Table Records
    ... a regular user, if you don't grant DELETE permission, he cannot delete. ... authentication and authenticate with Windows. ... Windows or is this SQL-assigned read permission done through SQL Server? ...
    (microsoft.public.sqlserver.security)
  • Re: Recover access to domain administrator
    ... >to deny access. ... >>I have denied access to domain administrator to sql ... but domain administrator still can not ... >>connect to sql server. ...
    (microsoft.public.sqlserver.security)
Loading