Post reply

GRANT

  • July 28, 2008 at 2:33 am

    #190222

    hi people

    can someone help me with a sql query to Grant Execute to all users to

    my stored procedure?

    thanks

  • July 28, 2008 at 3:22 am

    #849005

    select 'GRANT EXECUTE ON ' + name + ' TO PUBLIC'

    from sysobjects

    where xtype = 'P'

    copy and paste the result into new query window and run

  • July 28, 2008 at 3:33 am

    #849010

    It's usually recommended that no permissions be granted to public. Better way is to create a database role, grant the required permissions to the role then assign the role to the users.

    More work, yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 28, 2008 at 3:44 am

    #849016

    I would never ever grant permissions to public, set up a few groups like READ_ONLY READ_WRITE and app or what ever else you want, any table proc and so on you want write access too, just grant the permission to that. then do the same for the other groups.

    the command should look like this grant truncate on table to READ_WRITE and so on

    for you read_only group you will only want select permission.

    so you would do grant select on table to read_only

    Maybe your app group will have a mixture of diffrent permissions.

    Hope this helps.

    Terry

  • July 28, 2008 at 3:56 am

    #849026

    thanks guys, will create a db role first. then apply permission to it specifically.

    thanks

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

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