Hi All, I have a manager that needs to look at the definitions of a set of procs (about 175 of them) to pull the business logic out as it is undocumented.
I have created a report that puts all of the procs into a drop down so he can select them and then look at the definition using SQL as below:
SELECT
specific_name,
ROUTINE_DEFINITION
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
specific_name like '%update%' + CONVERT(VARCHAR(5),@except_no)
When I run this I get the definition just fine, when the report user runs this they get a NULL for the routine_definition. I have tried just granting the permissions to the view, and to the underlying tables, but the problem persists. I would prefer not to give the report user ddladmin privileges.
Is there a way around this?
Thanks in advance.