Generate GRANT for all tables reference by a procedure

Question

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?

Answer 1

The error message is correct. You can't say LIKE against the output of a query unless you are somehow explicitly constraining that output to one row (and this is not what you want to do). Just perform a join... you could weed out duplicates but there's no reason to (granting to the same table twice, if possible, does not hurt anything). Also please use QUOTENAME() instead of manually adding [ and ] yourself.

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT SELECT ON ' 
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'] TO [User1];'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.sql_dependencies AS d
ON d.referenced_major_id = t.object_id
INNER JOIN sys.procedures AS p
ON p.[object_id] = d.[object_id]
WHERE p.name = N'myproc';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Keep in mind that sys.sql_dependencies will not necessarily capture everything, for example if the stored procedure uses dynamic SQL, it can't parse the text to find all table references, or explore all possible permutations.