Grant permission for multiple SPs in one statement

Easwaran Paramasivam
Easwaran Paramasivam used Ask the Experts™
on
Is there any way to give execute permission for a user for MULTIPLE SPs in one statement?
Say for Example, I have many SPs in db. But I would like to give execute permission for SP1, SP2, SP3 and SP4. How to achieve in one statement instead of 4 statements? Please help.


GRANT EXECUTE ON [dbo].[STORED_PROCEDURE_NAME] TO ' + 'MYUSER'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Just run this script  and change login name to your org login name

and replace procedures name in where condition


PRINT 'GENERIC RIGHTS ASSIGNMENT SYSTEM STARTED'
DECLARE @LOGIN AS VARCHAR(100)
SET @LOGIN = 'TestUser'

DECLARE @DB AS VARCHAR(100)
DECLARE @QRY AS NVARCHAR(200)
DECLARE @OBJECT AS VARCHAR(100)
DECLARE @OBJTYPE AS VARCHAR(10)

Set @DB=''
Set @QRY=''
SET @OBJECT=''
SET @OBJTYPE=''


      Declare RIGHTSOBJCSR Cursor for
      SELECT [NAME],[TYPE] FROM sys.objects WHERE  type in (N'P')
      where [name] in (sp1, sp2, sp3, sp4)


      OPEN  RIGHTSOBJCSR
      fetch next from RIGHTSOBJCSR into @OBJECT,@OBJTYPE
      WHILE @@FETCH_STATUS=0
      BEGIN

            IF (@OBJTYPE=N'P' OR @OBJTYPE=N'PC' OR @OBJTYPE=N'FS')
                  BEGIN
                              
                              PRINT 'PROCEDURE SELCETED FOR RIGHTS ASSIGNING'
                              
                              SET  @QRY='GRANT view definition,exec ON ' + @OBJECT + ' TO ' + @LOGIN
                              EXEC (@QRY)
                              SET @QRY=''
                              PRINT 'PROCEDURE RIGHTS GIVEN'
                              
                  END

            ELSE
                  BEGIN      
                              
                              PRINT 'NO Sp found'
                              
                                                            
                  END

      fetch next from RIGHTSOBJCSR into @OBJECT,@OBJTYPE

      End
      
      Close RIGHTSOBJCSR
      Deallocate  RIGHTSOBJCSR


PRINT 'GENERIC RIGHTS ASSIGNMENT SYSTEM FINISHED'
Commented:
You can make a specific role
http://msdn.microsoft.com/en-us/library/ms187936.aspx

Grant the execution to that role. And for granting/revoking different users all that permisions in a single statement you grant/revoke them that role.
http://beyondrelational.com/modules/2/blogs/88/posts/10181/sql-server-custom-database-role.aspx
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial