For our ETL Processing we starting to use Macros and stored procedures vs leaving script buried inside BTEQ or Microsoft SSIS definitions. Is there any best practice for where the macros and/or stored procedures(SP) should be defined in?.
1. If it's a Update/Del/Insert operation store the macro/SP on the database where the table is located.
The access rights needed is if the macro is referencing data on another database. (grant select on database.table with grant option).
2. If it's an Update/Del/Insert operation store the macro/SP on the application user database.
The access rights need is select on the source data and ins/updt/del/statistics/sel (etc) on the target object. (with grant option)
3. If it's a Select Option store the macro/sp on a 'Views' database if the output is intended for end-users.
Same access right as #1. (which isn't any different than creating a view on "Views" database).
4. For any operation store the macro/SP in a centralized database where it's primary content is maybe only ETL related macros/stored procedures. Maybe there is 'test' and 'prod' databases.
Access rights to numerous objects will be needed based on what the macro/SP accesses and/or the ETL function.
So far we've been doing #1 and #3. I have a developer suggest #2.