Re: Execute SP

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/23/04

  • Next message: Dan Guzman: "Re: Running Antivurs on SQL servers" 
    Date: Thu, 23 Dec 2004 07:43:49 -0600

    If you need to grant EXECUTE to a role for a few number of procs, you can
    execute GRANT statements for use EM:

    GRANT EXEC ON MyProc TO MyProcExecuteRole

    If you want to grant EXECUTE to a role for all procs in the database, you
    can use a script like the one below:

    SET NOCOUNT ON

    DECLARE @GrantStatement nvarchar(4000)

    DECLARE GrantStatements CURSOR
    LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT
        N'GRANT EXECUTE ON ' +
        QUOTENAME(ROUTINE_SCHEMA) +
        N'.' +
        QUOTENAME(ROUTINE_NAME) +
        N' TO MyProcExecuteRole'
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE
        OBJECTPROPERTY(
            OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
                N'.' +
                QUOTENAME(ROUTINE_NAME)),
            'IsMSShipped') = 0 AND
        OBJECTPROPERTY(
            OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
                N'.' +
                QUOTENAME(ROUTINE_NAME)),
            'IsProcedure') = 1
    OPEN GrantStatements
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM GrantStatements
            INTO @GrantStatement
        IF @@FETCH_STATUS = -1 BREAK
        BEGIN
           RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
           EXECUTE sp_ExecuteSQL @GrantStatement
        END
    END
    CLOSE GrantStatements
    DEALLOCATE GrantStatements 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric Ward" <EricWard@discussions.microsoft.com> wrote in message 
news:74243249-0388-410B-A306-9248A1A60C79@microsoft.com...
> How do I give a roles access to execute a SP?
>
> Let me clarify:
> I know that I can create a role and then go give that role Execute
> permission to all the SP.
>
> I wondering if there is an easier way?
>
>
  • Next message: Dan Guzman: "Re: Running Antivurs on SQL servers"

    Relevant Pages

    • Re: Stored Procedure exec permission
      ... After that grant execute on that procedure ... deployed to a server running MSDE. ... I then added a login for my web app user. ...
      (microsoft.public.sqlserver.msde)
    • Re: How can I grant user run cmdexec
      ... First you can give grant execute on xp_cmdshell (gtrant execute on ... If they are not a member of the sysadmin role then they execute it under the ... prefix of the SQL Agent Proxy Account. ...
      (microsoft.public.sqlserver.security)
    • Re: how to generate a randomized "seqno" column
      ... "DBMS_CRYPTO" is owned by SYS but it seems execution it's not granted to anyone (but a public synonym DBMS_CRYPTO exists). ... If the user needs access to this package, then grant EXECUTE on the package to the user. ...
      (comp.databases.oracle.server)
    • Re: Grant Execute to user on procedures
      ... In Query Analyzer execute the below script with Text result. ... -- The above script will generate a script to grant execute previlage to ABC ...
      (microsoft.public.sqlserver.security)
    • Re: GRANT Select to all tables on a DB
      ... generate and/or execute the script yourself rather ... OPEN GrantStatements ... "Sam" wrote in message ... >>> I have three main database files on a SQL 2000 server. ...
      (microsoft.public.sqlserver.server)