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

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 12:01:00 -0400

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: SQL 2000 Sproc Role security bypassed when using QBF?
    ... I should add that the dynamically built Varchar is run using the syntax ... EXECUTE function requires the additional SELECT permission that sprocs ... I've got a Query By Form sproc that is used in a search process by ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... Columnist, SQL Server Professional ... but you could use the sp_msforeachtable sproc. ... Will grant select on every table in the current database to my_role. ... > roles and it is pain to run a query that give a permission on one table at ...
    (microsoft.public.sqlserver.security)
  • SQL 2000 Sproc Role security bypassed when using QBF?
    ... I've got a Query By Form (QBF) sproc that is used in a search process by one ... a SELECT permission for the USER instead of just EXECUTE on the sproc. ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... The db_reader role has SELECT permission on all tables and views. ... Columnist, SQL Server Professional ... have select permission to any table until I grant permission to specific ... > There might be another way, but you could use the sp_msforeachtable sproc. ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... Does db_reader role has select permission on all existing tables? ... > Columnist, SQL Server Professional ... > There might be another way, but you could use the sp_msforeachtable sproc. ... >> I am trying to figure out one single query will give a permission to all ...
    (microsoft.public.sqlserver.security)