How to grant Exec ON multiple Stored Procedures

Question

How to grant Exec ON multiple Stored Procedures

ArnolddG

SSC-Addicted

Points: 462
More actions
September 17, 2021 at 11:47 am

#3930495
Hi,
I have multiple Stored Procedures that are named like
_Test_01,
_Test_02
_Test_03
I would like to grant EXEC rights to multiple Stored Procedures at once.
Something like:
```
GRANT EXEC ON [dbo].[_Test*.*]  TO [udr_db_FocusOneUserRole] 
```
How can I Grant these in one line?

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

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Short of building dynamic T-SQL to make it happen, I'm pretty sure you can't. To individually grant permissions on objects, you need to grant them individually. One thing you can do, if they were in a different schema, is grant execute on the schema, which would get all those procedures in a single line.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ArnolddG SSC-Addicted Points: 462 More actions · Answer 2

mmm, not what U hoped for, but it is a clear answer, though.

Thanks for your help, Grant

Michael L John One Orange Chip Points: 26551 More actions · Answer 3

Generate the code with a query

SELECT  
'GRANT EXEC ON [' + S.name + '].[' O.name + ']  TO [udr_db_FocusOneUserRole];' 
FROM sys.objects O
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE O.name LIKE '_Test&.&'
AND O.type = 'P'

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ArnolddG SSC-Addicted Points: 462 More actions · Answer 4

Thanks, Michael,

The query works fine, and it shows my list of Stored Procedures.

It does not seem to 'execute' this Query as a part of my script though.

What am I missing?

Thanks again...

Michael L John One Orange Chip Points: 26551 More actions · Answer 5

ArnolddG wrote:

Thanks, Michael,
The query works fine, and it shows my list of Stored Procedures. It does not seem to 'execute' this Query as a part of my script though.
What am I missing?
Thanks again...

It's not going to execute anything. You need to copy the results into a new query window and execute that

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ArnolddG SSC-Addicted Points: 462 More actions · Answer 6

Ah, I see

I had hoped I could use it in an SQL Script...

Michael L John One Orange Chip Points: 26551 More actions · Answer 7

Well, you can. You will need to select each SQL statement generated into a variable, and execute that.

That can be done by building one large string of commands, and executing that.

Or, you can create a cursor and execute each one.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 26551 More actions · Answer 8

Maybe this?

DECLARE @SQL nvarchar(max)
SELECT  @SQL = 
STRING_AGG( 
CONVERT(NVARCHAR(max), 'GRANT EXEC ON [' + S.name + '].[' + O.name + ']  TO [udr_db_FocusOneUserRole]'), ';'   ) 
FROM sys.objects O
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE O.name LIKE '%YourString%'
AND O.type = 'P'

EXEC(@SQL)

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

As Grant suggests, the use of Dynamic SQL would be the ticket here. Use a pattern search (LIKE) on the names of stored procedures in the sys.procedures system view to find the procedure names and build the dynamic SQL from those.

Since you posted in a 2019 forum, you must have STRING_AGG and that makes your problem easy. This should work.

DECLARE  @Pattern    SYSNAME = N'[_]Test[_][0-9][0-9%'
        ,@UserOrRole SYSNAME = 'udr_db_FocusOneUserRole'
        ,@SQL        NVARCHAR(MAX)
;
 SELECT @SQL = STRING_AGG(CONCAT('GRANT EXEC ON ',v.SchemaName,'.',v.ProcName,' TO ',v.UserOrRole,';'),NCHAR(10))
   FROM sys.procedures
  CROSS APPLY (VALUES (QUOTENAME(SCHEMA_NAME(schema_id))
                      ,QUOTENAME(name)
                      ,QUOTENAME(@UserOrRole))
               )v(SchemaName,ProcName,UserOrRole)
  WHERE name LIKE @Pattern
;
--===== Display the dynamic SQL
  PRINT @SQL
;

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Ah... didn't see Mike post while I was typing. I also meant to say that once you're sure the code it produces is correct, change the PRINT @SQL to EXEC (@SQL) and Bob's your uncle.

I also used some system functions to avoid having a JOIN. It doesn't make much difference except to cut down on the FROM clause a bit.

--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)
Intro to Tally Tables and Functions

Michael L John One Orange Chip Points: 26551 More actions · Answer 11

Jeff Moden wrote:

Ah... didn't see Mike post while I was typing. I also meant to say that once you're sure the code it produces is correct, change the PRINT @SQL to EXEC (@SQL) and Bob's your uncle.
I also used some system functions to avoid having a JOIN. It doesn't make much difference except to cut down on the FROM clause a bit.

Great minds...

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ArnolddG SSC-Addicted Points: 462 More actions · Answer 12

Thanks for all of your collective help.

I have enough input to work with now.

Regards,