Professional Web Applications Themes

How to grant permissions to all tables in the database - Microsoft SQL / MS SQL Server

Hello: I need to create a stored procedure to grant the SELECT permissions to all tables in the database. What would be the easiest way to do this? The basic syntax is: CREATE PROC uspGrant_Permissions AS GRANT SELECT ON dts_Imports.dbo.WA_INK TO "specialty\SQLUsers" GO But it works for one table only. I would appreciate your help. Thank you, -- Peter Afonin...

  1. #1

    Default How to grant permissions to all tables in the database

    Hello:

    I need to create a stored procedure to grant the SELECT permissions to all
    tables in the database. What would be the easiest way to do this?

    The basic syntax is:

    CREATE PROC uspGrant_Permissions
    AS
    GRANT SELECT
    ON dts_Imports.dbo.WA_INK
    TO "specialty\SQLUsers"
    GO

    But it works for one table only.

    I would appreciate your help.

    Thank you,

    --
    Peter Afonin


    Peter Guest

  2. #2

    Default How to grant permissions to all tables in the database

    There's an excellent undoented procedure that can help
    you out.

    sp_MSForEachTable 'GRANT SELECT ON ?
    TO "specialty\SQLUsers"'

    The ? is replaced with each table_name during execution.

    To see it an innocuous execution, try this:

    sp_MSForEachTable 'SELECT TOP 1 * FROM ?'

    Good luck!

    --Angel
    --------------------------------
    Every time I lose my mind,
    I wonder if it's really worth finding.

     
    permissions to all 
    do this? 
    AngelWPB Guest

  3. #3

    Default Re: How to grant permissions to all tables in the database

    Peter,

    Two options..
    One would be to use the undoented call sp_msforeachtable - , as in
    GRANTING SELECT on all tables to database user 'username'

    CREATE PROC uspGrant_Permissions
    AS
    EXEC sp_msforeachtable 'EXEC(''GRANT SELECT ON ? TO username'')'

    Since its a undoented call, please refrain from using it in production
    code.

    Another option would be to generate sql scripts, as in

    SELECT 'GRANT SELECT ON '+table_name+' TO username'+char(13)+'GO'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_type = 'BASE TABLE'
    AND table_schema='dbo'

    This would generate a series of GRANT SELECT ON .. commands for all the
    tables for that owner.You can then copy this code and execute it .


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Peter Afonin" <com> wrote in message
    news:%phx.gbl... 


    Dinesh.T.K Guest

  4. #4

    Default Re: How to grant permissions to all tables in the database

    sp_msforeachtable is "undoented" that means you use it at your own risk.
    I would suggest writing your own cursor and issue the grant statement. This
    way you know exactly how your code works.

    btw, this sp uses a cursor internally.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Peter Afonin" <com> wrote in message
    news:#phx.gbl... 


    oj Guest

  5. #5

    Default Re: How to grant permissions to all tables in the database

    You're welcome. Also check out sp_MSForEachDB.
     [/ref]
    help 
    >> permissions to all [/ref][/ref]
    to [/ref]
    >
    >
    >.
    >[/ref]
    AngelWPB Guest

  6. #6

    Default Re: How to grant permissions to all tables in the database

    Thank you, Dinesh.

    sp_msforeachtable works great for me.

    Peter

    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:%phx.gbl... [/ref]
    all 
    >
    >[/ref]


    Peter Guest

Similar Threads

  1. Using WMI to grant permissions to new users on folder..
    By scsharma in forum ASP.NET Security
    Replies: 11
    Last Post: September 28th, 07:05 PM
  2. Database permissions
    By John in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: February 9th, 02:13 AM
  3. Minimal permissions with an Access Database and 2000 server ?
    By Gilles LAMBERT in forum ASP.NET Security
    Replies: 2
    Last Post: October 23rd, 04:42 AM
  4. Database permissions
    By Trumpet in forum ASP Database
    Replies: 1
    Last Post: July 14th, 04:57 PM
  5. Database/Folder Permissions
    By Jordan Marton in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 14th, 03:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139