Archives of the TeradataForum

Message Posted: Wed, 03 Aug 2005 @ 19:11:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Srikanth,

I may be wrong. But I believe the dynamic sql restriction is misunderstood. I believe it works in this situation.

Create a User and name it something like PROD_PROCS. Then create the stored procedures in PROD_PROCS. It does not matter who creates the stored procs. Then you can use any user to CALL. Proper permission must be given to the calling User and PROD_PROCS user. The calling user just needs Execute procedure on PROD_PROCS. The user PROD_PROCS needs nay access needed by the stored procedures with the grant option. For example, the stored procedure does select, insert, update and delete on database PROD_Tables. The grant would be

GRANT select, insert, update, delete on PROD_TABLES
      to PROD_PROCS with grant option.

You never need to logon as PROD_PROCS.

This setup has worked fine for us here and have not found any restrictions yet. It also has an advantage for making backups of objects easier.

Again if someone knows better, please let me know.

Thanks,

Bob Diehl
Travelcoity.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>