Sql-server – what is the best approach to send report by Email

You can create triggers that are fired upon insert/delete/update in the table to write the data you need in a separate history table. You can then later query this table to create your reports. No need to change the application that is now responsible for maintaining the data.

Since SQL Server Agent does not support using a Windows Group as the owner of a SQL Server Agent job. You can do it, but it means an end run around the built in restrictions.

You can use some code that is posted at the link below.

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This approach does an 'end run' around the "owner" of the SQL Agent job for all practical purposes of execution. This is because the dbo.StartAgentJob stored procedure uses the Logins and Groups in the dbo.msdbJobMap table to decide who can run a SQL Agent Job. As written, it will include any member of a group assigned to the job, has the rights to start the job.

This means that anyone with rights to create a SQL Server Agent Job (a member of SQLAgentReaderRole or higher) can create and modify their own josb.

If they also have rights to insert the proper GroupName and JobName in the dbo.msdbJobMap table, anybody in those groups assigned to the job can execute the job as well.

Although a GROUP does not own the job directly, the dbo.StartAgentJobstored procedure will use its elevated permissions to determine whether to allow the Login and/or members of a Group to execute this job.

What still remains as a problem?

The owner of the SQL Agent Job is the only one who can edit the job contents. (Of course, a sysadmin can do almost anything.)

If the SQL Agent job needs to be rewritten and the owner no longer exists in your system, the sysadmin can change the job owner from the defunct account to the new designated owner. (Or script the job out and give the script to the developer who will do the rewrite and give the new job a new name.)

So, it is not a 100% solution to make the developers completely independent from the administrators, but it should Greatly Reduce the interactions needed.

Footnote: If the job is to run on distributed servers this is not enough.