I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select statement for all the table that this stored procedure uses. I need this since my procedures use sp_executesql and hence users need explicit permissions on the tables as sp_executesql makes execute permission on the stored procedure not enough (as you might know the user would need select permission on the tables used by that SP as well).
I have so far done the below dynamic SQL statement, however, it only works if the stored procedure only selects from one table. I totally understand that it does not work for more due to the subquery in the where clause.
select 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [User1]'
from sys.tables
where name like (SELECT distinct t.name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id
where P.NAME LIKE 'myproc')
Error when SP uses more than 1 table:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
My required output for EACH table used by a stored procedure:
GRANT SELECT ON [dbo].[table1] TO [User1]
Is there another way to achieve this?