locked
how to grant select on all views OR all columns RRS feed

  • Question

  • grant SELECT   on all_columns to yogi_bear;

    grant SELECT   on all_objects  to yogi_bear;

    grant SELECT   on all_views    to yogi_bear;

     

    but it doesn't work, why? I know how to do it with a cursor or dynamic sql, but why won't these sql statements work?

    Wednesday, December 14, 2011 12:55 AM

Answers

  • without using cursor or loop stmt.. it won't be possible to grant SELECT to all objects/views...

    your command will throw error "Cannot find the object 'all_columns', because it does not exist or you do not have permission"

    below will work

    SELECT

    'GRANT SELECT ON ' + TABLE_NAME + ' TO USER'

    FROM

    INFORMATION_SCHEMA.Tables

     


    VVinayPrasad
    • Marked as answer by BlueIzzzz Wednesday, December 14, 2011 6:47 PM
    Wednesday, December 14, 2011 8:50 AM

All replies

  • without using cursor or loop stmt.. it won't be possible to grant SELECT to all objects/views...

    your command will throw error "Cannot find the object 'all_columns', because it does not exist or you do not have permission"

    below will work

    SELECT

    'GRANT SELECT ON ' + TABLE_NAME + ' TO USER'

    FROM

    INFORMATION_SCHEMA.Tables

     


    VVinayPrasad
    • Marked as answer by BlueIzzzz Wednesday, December 14, 2011 6:47 PM
    Wednesday, December 14, 2011 8:50 AM
  • To grant him access on all tables:
       GRANT SELECT TO freddie
    To grant freddie access on objects in a schema:
       GRANT SELECT on SCHEMA::dbo TO freddie

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Uwe RickenMVP Wednesday, December 14, 2011 1:32 PM
    Wednesday, December 14, 2011 10:14 AM
  • To grant him access on all tables:
       GRANT SELECT TO freddie
    To grant freddie access on objects in a schema:
       GRANT SELECT on SCHEMA::dbo TO freddie

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    perfect
    VVinayPrasad
    Wednesday, December 14, 2011 10:45 AM
  • Why does public have that permission in sql under the msdb db? if you look at the permissions to public you will find it has SELECT permission to  object "all_columns", so why can I not assign permission to that object?
    Wednesday, December 14, 2011 4:42 PM
  • I knew that but not the answer I was looking for and it appears everyone will ignore my real question for what ever reason. But I asked the same question differently in another thread... but thanks for the efforts...
    Wednesday, December 14, 2011 6:47 PM
  • Why does public have that permission in sql under the msdb db? if you look at the permissions to public you will find it has SELECT permission to  object "all_columns", so why can I not assign permission to that object?

    Where are you looking? If you look at Securable pages in SSMS, pay attention to what's on the left, the schema. sys.all_columns is a catalog view which is a superset of sys.columns.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 15, 2011 10:53 PM
  • Thanks a lot VVinayPrasad!!!!!

    As simple as that!. Worked great. I was looking for a script to grant select on every view on a database. I just had to adjust your script to read views instead of tables.

    SELECT

    'GRANT SELECT ON ' + TABLE_NAME + ' TO USER'

    FROM

    INFORMATION_SCHEMA.Views

    THANKS AGAIN!

    Wednesday, December 2, 2015 2:51 PM