Post reply

Grant access to login to run bunch of SP

  • June 18, 2012 at 12:03 pm

    #271432

    All,

    I need to give permission to run stored procedure for login. There are bunch of SP in the databases. I don't want to go each SP properties to do that. Is there anyway I can do it easily. Please advise.

    Thanks.

  • June 18, 2012 at 12:21 pm

    #1502387

    it depends.

    if the user is going to get access to ALL stored procedures no matter what, it's a single command.

    if it is a group of specific procedures, then it's a per proc basis, but you can use a SELECT statement to buidl the commands for you.

    here's a basic example:

    CREATE ROLE [MyProcCaller]

    SELECT 'GRANT EXECUTE ON ' + quotename(name) + ' TO [MyProcCaller]'

    FROM sys.procedures

    WHERE name like 'pr_%'

    OR name = 'BuildInvoices'

    OR name IN('CLR_CopyFile',

    'CLR_DeleteFile',

    'CLR_DeleteFiles',

    'CLR_ExportTableAppendToCSV',

    'CLR_ExportTableAppendToCustomDelim',

    'CLR_ExportTableAppendToFixedWidth',

    'CLR_ExportTableAppendToTSV',

    'CLR_ExportTableToCSV')

    --now add our users to this role:

    EXEC sp_addrolemember N'MyProcCaller', N'bob'

    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!

  • June 18, 2012 at 12:23 pm

    #1502390

    ideally, you would create a database role then grant to that role the EXECUTE permission on the schema(s) that contain the stored procedures. Then add that role to any database login that needs the execute permissions.

    Save yourself some headaches and grant permissions on schema instead of individual procedures. If there are particular procedures that need to be 'secured' with different permissions then put them in separate schema. Just make sure all the schema(s) are owned by dbo (or at least the same user) to prevent ownership chain issues.

  • June 18, 2012 at 3:34 pm

    #1502485

    Thank you Lowell & andersg98. Really appreciate it.

    Regards,

    SueTons

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

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