CODEWITHSUNDEEP
t-sqlsql-server-2012
EvilDr
EvilDr

Reputation: 9610

Grant Exec on a set of stored procedures

I have about 500 procedures in a database that are prefixed with mysp_.

Currently I have two roles, Role1 and Role2 and permission to execute the procedures is spread amongst them.

I need to delete Role2 (which isn't a problem), and then Grant Exec on every one of my procedures to Role1.

Can anyone advise how to do this please? I can easily Grant on a single procedure, but not on a selection of procedures (e.g. those prefixed with mysp_).

Upvotes: 0

Views: 67

Answers (2)

lee87
lee87

Reputation: 70

You could use dynamic SQL and a cursor to iterate over the procedures:

DECLARE @ProcName VARCHAR(100)
DECLARE @SQL VARCHAR(100)
DECLARE DynamicSQL CURSOR FOR
SELECT Name FROM sysobjects WHERE xtype = 'P' and Name like 'mysp_%'

OPEN DynamicSQL

FETCH NEXT FROM DynamicSQL INTO @ProcName
WHILE @@Fetch_Status = 0
BEGIN

  SET @SQL = 'GRANT EXEC ON ' + @ProcName + ' TO Role1'
  PRINT @SQL
  --EXEC(@SQL)

FETCH NEXT FROM DynamicSQL INTO @ProcName
END

CLOSE DynamicSQL
DEALLOCATE DynamicSQL

Once you're happy with the output, just uncomment the EXEC part!

Upvotes: 1

marc_s
marc_s

Reputation: 755491

How about this?

SELECT
    'GRANT EXECUTE ON ' + OBJECT_SCHEMA_NAME(object_id) + '.' + name + ' TO Role1'
FROM 
    sys.procedures
WHERE
    Name LIKE 'mysp%'

This will generate as output a list of statements, which you can copy from the output grid into a new query window and then execute - and now all your procedures are granted to Role1 !

Upvotes: 1

Related Questions