I’ve a few saved procedures in my DB wherein every one choose from a few tables. I must assemble a press release the place I may give it a saved process and get the grant choose assertion for all of the desk that this saved process makes use of. I want this since my procedures use sp_executesql and therefore customers want express permissions on the tables as sp_executesql makes execute permission on the saved process not sufficient (as you may know the consumer would want choose permission on the tables utilized by that SP as effectively).
I’ve to date finished the beneath dynamic SQL assertion, nevertheless, it solely works if the saved process solely selects from one desk. I completely perceive that it doesn’t work for extra as a result of subquery within the the place clause.
choose 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [User1]' from sys.tables the place identify like (SELECT distinct t.identify 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 the place P.NAME LIKE 'myproc')
Error when SP makes use of greater than 1 desk:
Subquery returned greater than 1 worth. This isn’t permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
My required output for EACH desk utilized by a saved process:
GRANT SELECT ON [dbo].[table1] TO [User1]
Is there one other technique to obtain this?