Post reply

granting execute privilege on all stored procs in a database to a user

  • February 13, 2012 at 2:18 pm

    #264504

    Hi,

    I want to grant execute privilege of all the stored procs (200+) in a database to a user.

    Does any of you have an automation script for this..?

    Thanks!

  • February 13, 2012 at 2:40 pm

    #1446528

    USE databaseName

    GRANT EXEC ON SCHEMA::dbo TO userName

    That should do the trick, assuming the schema for these objects is dbo! Else, replace dbo with the schema you need.

    Jared
    CE - Microsoft

  • February 13, 2012 at 2:42 pm

    #1446530

    Thanks Jared..

    I was thinking of granting individual object privileges..

    This is much easier solution.

    Thanks!!!

  • February 13, 2012 at 2:54 pm

    #1446540

    Siva Ramasamy (2/13/2012)

    Hi,

    I want to grant execute privilege of all the stored procs (200+) in a database to a user.

    Does any of you have an automation script for this..?

    Thanks!

    Try reading this:

    http://msdn.microsoft.com/en-us/library/ms173848.aspx

    Be careful of granting Dbo (Data base owner) priviliges, it does allow for a great deal more that just executing stored procedures.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 13, 2012 at 3:08 pm

    #1446546

    Hi Ron,

    I don't see anything in that reference to dbo or specific to the EXECUTE permission. Can you elaborate on issues with granting the exec permission on the dbo schema of a database?

    Jared
    CE - Microsoft

  • February 13, 2012 at 3:55 pm

    #1446570

    SQLKnowItAll (2/13/2012)

    Hi Ron,

    I don't see anything in that reference to dbo or specific to the EXECUTE permission. Can you elaborate on issues with granting the exec permission on the dbo schema of a database?

    I took your schema dbo to be dbo_owner if that is true (and I know that assumptions can and will make an a$$ out of myself). But if I am correct then run this:

    USE MASTER

    go

    sp_dbfixedrolepermission @rolename ='db_owner'

    This will return a list of 59 permissions

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 13, 2012 at 7:25 pm

    #1446600

    bitbucket-25253 (2/13/2012)

    SQLKnowItAll (2/13/2012)

    Hi Ron,

    I don't see anything in that reference to dbo or specific to the EXECUTE permission. Can you elaborate on issues with granting the exec permission on the dbo schema of a database?

    I took your schema dbo to be dbo_owner if that is true (and I know that assumptions can and will make an a$$ out of myself). But if I am correct then run this:

    USE MASTER

    go

    sp_dbfixedrolepermission @rolename ='db_owner'

    This will return a list of 59 permissions

    I still don't understand because I am only granting the EXEC permission to the user on the objects with dbo schema. I am not making the user the owner. If I did, I would not need to explicitly grant EXEC to the user. I have just verified that creating a user 'test' with db_reader role mapped to AdventureWorks does not give db_owner access to the user when granted EXEC on schema::dbo

    I cannot delete or insert either. So, I'm not sure what I am missing here.

    Jared
    CE - Microsoft

  • February 13, 2012 at 9:33 pm

    #1446618

    Go to this site

    http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server

    Or

    •Using SQL Server Management Studio, expand Security then Schemas under a particular database.

    •Double-click on the schema name Dbo and choose Properties.

    In the next window click on "View server permission"

    In the next window click on "View database permission"

    and there they will be displayed.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 13, 2012 at 9:44 pm

    #1446623

    SQLKnowItAll (2/13/2012)

    USE databaseName

    GRANT EXEC ON SCHEMA::dbo TO userName

    That should do the trick, assuming the schema for these objects is dbo! Else, replace dbo with the schema you need.

    That could be might handy. I could look it up but let me ask instead... will that work on a database ROLE as well?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2012 at 7:25 am

    #1446842

    bitbucket-25253 (2/13/2012)

    Go to this site

    http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server

    Or

    •Using SQL Server Management Studio, expand Security then Schemas under a particular database.

    •Double-click on the schema name Dbo and choose Properties.

    In the next window click on "View server permission"

    In the next window click on "View database permission"

    and there they will be displayed.

    Ok, after reading this and looking at the permissions, I believe that if I want to grant a user the privilege to execute all stored procedures, but not other permissions, you use GRANT EXEC ON SCHEMA::dbo TO user. So are you saying my suggestion was correct? Maybe I am still unclear of what you are trying to make me aware of.

    Jared
    CE - Microsoft

  • February 14, 2012 at 7:27 am

    #1446843

    Jeff Moden (2/13/2012)

    SQLKnowItAll (2/13/2012)

    USE databaseName

    GRANT EXEC ON SCHEMA::dbo TO userName

    That should do the trick, assuming the schema for these objects is dbo! Else, replace dbo with the schema you need.

    That could be might handy. I could look it up but let me ask instead... will that work on a database ROLE as well?

    I had to look it up. Found it here.

    /* CREATE A NEW ROLE */

    CREATE ROLE db_executor

    /* GRANT EXECUTE TO THE ROLE */

    GRANT EXECUTE TO db_executor

    Jared
    CE - Microsoft

  • February 14, 2012 at 9:20 am

    #1446915

    SQLKnowItAll (2/14/2012)

    Jeff Moden (2/13/2012)

    SQLKnowItAll (2/13/2012)

    USE databaseName

    GRANT EXEC ON SCHEMA::dbo TO userName

    That should do the trick, assuming the schema for these objects is dbo! Else, replace dbo with the schema you need.

    That could be might handy. I could look it up but let me ask instead... will that work on a database ROLE as well?

    I had to look it up. Found it here.

    /* CREATE A NEW ROLE */

    CREATE ROLE db_executor

    /* GRANT EXECUTE TO THE ROLE */

    GRANT EXECUTE TO db_executor

    Ah. Thanks, Jared. I actually already do that particular thing. Thanks for your time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 15, 2012 at 1:37 pm

    #1447532

    To accomplish initial requirement, try this:

    USE dbname

    GO

    SELECT 'GRANT EXECUTE ON ' + SCH.name + '.' + PRC.name + ' TO [someuser]' AS Permission

    FROM sys.schemas AS SCH

    INNER JOIN sys.procedures PRC

    ON SCH.schema_id = PRC.schema_id

    WHERE PRC.is_ms_shipped = 0 and PRC.type = 'P'

    ORDER BY SCH.name, PRC.name

    --Run as text and copy and paste to another query window to run

Viewing 13 posts - 1 through 12 (of 12 total)

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