Re: Grant select permission on all Tables to a DB role

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 12:43:09 -0400

The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script? 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:
> Why can't you just add the role to db_datareader and db_datawriter?
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
>
> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
>
> But I digress....
>
> sp_msforeachtable 'grant select on ? to my_role'
>
> Will grant select on every table in the current database to my_role.
>
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
>
> http://www.dbazine.com/larsen5.shtml
>
>
>
> "SangHunJung" <SangHunJung@discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@microsoft.com...
> > Hello,
> > I got Win2k Advanced Server with SQL 2K running.
> > I am trying to figure out one single query will give a permission to all
> > tables under a database.   A database has 20 - 30 tables with several
> custom
> > roles and it is pain to run a query that give a permission on one table
at
> a
> > time.
> > Grant select on my_table to my_role
> > Grant update on my_table1 to my_role1
> > Can any one turn a light for me on this??
> >
> > Thanks in advance.
> > SangHun
>
>
>

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: SQL CLR Sproc isnt running right
    ... did you grant rights using the SQL Server 2005 Surface Area Config tool? ... and I got a permissions error back saying DBO doesn't have permission to insert records into the table... ... but the assemblie's stored procedures do all have execute permissions set for the role that the executing user is in... ... "William Vaughn" wrote in message ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Error !!! SQL XML Bulkload Please help
    ... Irwin Dolobowsky ... > The schedule job gets run by SQL server Agent and the SQL server agent is ... > which has also got the administrator permission. ... Is the scheduled job running as a different user? ...
    (microsoft.public.sqlserver.xml)
  • System.Security.SecurityException with .NET 2005 Only
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)
  • After 2003 to 2005 Upgrade Wizard, Getting System.Security.SecurityException
    ... Crystal Report from a SQL Server 2000 table. ... Friend WithEvents scnSQLConnection As _ ... The dialog box suggests clicking on "Add Permission to the Project." ... Restart the debugging session, same problem again. ...
    (microsoft.public.dotnet.languages.vb)