Ah... understood. I thought you were trying to "illegally" bypass established protocols of development in your company. Thanks for the clarification.

I believe this could be done in a rather simple manner if I'm reading the requirements correctly. I'm reading the requirements simply as there are several reports that need to be created and then certain reports need to be sent to certain groups of people.

I'm also reading that one of the biggest requirements is as you mentioned...

I'm no SSRS nor SSIS expert so you'll need to figure out how to get either to save a file (I've been led to believe that's a simple task) but I believe that I'd do this as follows. I realize that this is just a functional overview but you have to start somewhere. As they say, you can't correct a blank piece of paper. 😉 As it turns out, this is a combination of both your "A" and "B" methods.

1. Create several nightly (or whatever schedule you need) jobs to create the reports as either PDF's or Spreadsheets depending on the need and have the system save them in a disk folder dedicated to the task.

2. Create a table that contains the report name, static file name, and the email address of each person to receive the report. There would be one row per report name/email address combination. It might also server you well to have a start and end date for each row so you have a history of who was enabled to receive which report and when. If you decide to do this (and I do recommend it), make the default end date '9999' which will be converted to 9999-01-01 for and end date. This will keep you from having to mess around with using NULL as an open end date. For reference purposes, I'd call this table something like 'ReportSubscription' (I almost never pluralize table names... I name tables after what a single row contains).

As a bit of a side bar, you should probably create more than just one table so that you can do a little normalization and establish some DRI (Declared Referential Integrity) for report and file names, etc. It's a minor complexity that almost always pays off in the long run.

3 The rest is fairly easy. Have a single job run a single stored procedure that that reads and finds the distinct values of all report names from the ReportSubscription table where "now" is between the start and end dates. Using the FOR XML PATH trick for concatenation, concatenate all of the "active" ("now" falls between the start and end dates) email addresses into the "to" list for each report, add the filename of the report for each report as a file attachment, and use all that to build the sp_senddbmail command for each report. Then execute each command. The construction of all of these commands can actually be done using a single well formed and fairly easy to construct SELECT to put all of these commands into a single variable and then just execute that one variable.

What all of that does for you is simple... it allows simple subscription management just by updating a small table or two. Therefor, privs need to only be granted to that small table or two. It will also keep a history of who was scheduled to get which report and when. Neither the stored procedure nor the job to send the emails will ever need to be changed. Only the content of the small table or two will need to be changed.

As yet another sidebar, you could also put a time to send on each report listing in a separate table and run the job several times a day. This would allow you to easily control multiple transmissions of the same (updated) report or when a single report was supposed to be sent. Of course, the jobs to create the reports would need to execute more than once per day if the report needed to be sent more than once per day.

The advantage of storing the reports in a dedicated disk folder is that if someone needed to see the report on an ad hoc rather than scheduled basis, they could simply retrieve it instead of needing privs to run the report creation job and the email job.