How granting the execute permission for several stored procedures - SQL Server 2019

Question

Hi,

in order to review some security aspects about a SQL Server 2019 instance I need to assign the execute permission for several stored procedures to a db_datareader and db_datawriter user without operating one by one.

How could I accomplish a such task, please? Thanks

Answer 1

Hi @pmscorca

Hi, the stored procedures are almost 800. I need to grant the execution permission to the all SPs.

Try this code:

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql += 'GRANT EXECUTE ON [' + s.name + '].[' + p.name + '] TO [UserName];' + CHAR(13)
FROM sys.procedures p JOIN sys.schemas s ON p.schema_id = s.schema_id;
PRINT @sql -- Check the generated GRANT statements
EXEC sp_executesql @sql;

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

It's not possible to write a script, that can guess which SP you want to grant execute permissions and which one not.

About how many SP do we talk? 10, 100, 1000?

Answer 3

If they all are in the same schema, and the users are supposed to have access to all procedures in that schema, it's simple:

GRANT EXECUTE ON SCHEMA::dbo TO whateverrole

If we are talking 800 procedures out of a couple of thousand in the same schema, there is no choice: you need to grant them one by one. You will need to be creative in the process. If you have a list of the procedures, load them into a good text editor with support for regular expressions. SSMS is such an editor.

Starting point:

I press Ctrl-H, and I fill in:

Note that I've clicked the .* icon for regular expressions. The ^ stands for beginning of line.

I press the right most button, and now I have:

Now I change the find/replace window to:

The $ stands for end-of-line. I press Replace All again, et voilà!

Eh, wait! The result in SSMS 21 was not really as expected, but SSMS 20 seem to do it right. Or try another editor - regular expressions in SSMS have always seemed spooky to me