We use Log Shipping to allow Business Analysis users to query data. I would like to email the Log Shipping Status report to users to see last time data is refreshed.
When I Right Click on Server--> Standard reports --> It says members of the SysAdmin Report can only view this.
How can I give access, or email Report Status to Users of the report? I prefer to keep the same color html format if possible.
You can schedule below SQL to run daily with a SQL Agent job or if you have SSRS you can easily schedule and format the report. It will give you most of the information from the standard reports.
select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server
from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
on lms.secondary_id=lsd.secondary_id
For HTML email status report use this answer: Here's how can you create the HTML body part of your mail
