Grant SP permission with condition

Question

Hi all.
I have two questions:
1) I want to grant ALTER and EXECUTE permission to the all SP that it's name like N'%Report%' for a particular User. (for example Report12)
What should I do?

2) Also when I create a new SP that it's name is like N'%Report%', these permissions will grant for a particular User.
What should I do?

Answer 1

Hi

Read the following article on "SQL Server Security Model"

http://www.databasejournal.com/features/mssql/article.php/3713116/SQL-Server-Security-Model.htm

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 2

For the first question:

SELECT 'GRANT ALTER, EXECUTE  ON ' +
       quotename(s.name) + '.' + quotename(o.name) + ' TO Report12'
FROM   sys.objects o
JOIN   sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.name LIKE N'%Report%'

Copy and paste the result to a query window.

For the second query, you would need a DDL trigger to generate GRANT
statements.

On the other hand, if all these procedures were in a separte schema your
prospects would be better. You could grant the user EXECUTE and ALTER rights
on the schema to the user and that should be enough.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Thank you Erland Sommarskog

This code Resolve my first problem:

SELECT 'GRANT ALTER,EXECUTE ON '+quotename(s.name) + '.' + quotename(o.name) + ' TO palborz'
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.name LIKE N'%Report%' and o.type='p'
******************

But please Explain more for generate GRANT statements by Trigger.

---

Each problem has a solution.

Answer 4

But please Explain more for generate GRANT statements by Trigger.

I didn't go into detail, because I don't think it is a good solution. I recommend that you reconsider your design and put these procedure in a separate schema instead.

You can read about DDL triggers in Books Online.

---

Erland Sommarskog, SQL Server MVP, [e-mail]