Post reply

Need to add and grant permissions to all the tables,procs from a database to a database role

  • March 16, 2016 at 12:51 pm

    #325721

    Hi,

    I have a database role currently in my database and i am trying to add the all the objects(tables and stored procs from this database) to it and grant the permissions.

    I know i can use something like below for one table and procedure, but i have to do it for all the tables and procedures of a database

    USE database

    GRANT SELECT ON dbo.table1 TO samplerole ;

    GRANT EXEC ON dbo.usp_proc TO samplerole ;

    EXEC sp_addrolemember N'samplerole ' N'dbo.table1';

    EXEC sp_addrolemember N'samplerole ' N'dbo.usp_proc';

    Is there a faster way of adding and giving permissions to all the tables and stored proceures to a database role

  • March 16, 2016 at 1:08 pm

    #1865715

    typically, you grant on a per-table or per-procedure basis when you want to restrict access to SOME of the tables.

    what happens if new tables are added, or a table/proc is dropped and recreated? you'll have to re-grant per-object permissions again.

    if you need to grant access to all, i would grant db_datareader and execute to the role.

    this is how i might tackle that:

    CREATE ROLE DevelopersReadAccess;

    GRANT VIEW DEFINITION TO [DevelopersReadAccess];

    GRANT EXECUTE TO [DevelopersReadAccess];

    EXEC sp_addrolemember ''db_datareader'',''DevelopersReadAccess'';

    if you really want to do per table and per procedure , you use the metadata to generate your commands for you, ie

    SELECT 'GRANT SELECT ON '

    + quotename(schema_name(schema_id))

    + '.'

    + quotename(name)

    + ' TO samplerole ;'

    FROM sys.tables samplerole

    SELECT ' GRANT EXECUTE ON '

    + quotename(schema_name(schema_id))

    + '.'

    + quotename(name)

    + ' TO samplerole ;',*

    FROM sys.procedures WHERE type_desc='SQL_STORED_PROCEDURE'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 16, 2016 at 1:13 pm

    #1865716

    Thank you Lowell!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply