SELECT * FROM system.storedprocedures returns empty cursor if user is not ADSSYS

Question

As stated on the title, the select statement to retrieve the list of all system.storedprocedures from an ADS DataDictionary only returns information when the user is ADSSYS. I suppose it is a matter of user permissions. The question then is, how do I assign permission to a given user or usergroup to query using select from system.storedprocedures?

Thank you,

Reinaldo.

Answer 1

You need to grant the user or group EXECUTE permissions on each individual stored procedure. With the EXECUTE permission they will be able to see the procedure & its parameters, but no other properties. If you grant them the ALTER permission they will be able to see the SQL script or DLL properties of the procedure as well.

You can assign permissions using Advantage Data Architect (ARC), with SQL GRANT statement, or with the ACE API AdsDDGrantPermission. Of course, in order to modify permissions you'll need to be the ADSSYS user or a user/group with ALTER permissions on the other user/group.