Sql-server – Generate GRANT for all tables reference by a procedure

dynamic-sqlpermissionssql serverstored-procedures

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?

Best Answer

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.

Related Question