October 15, 2010 at 1:19 pm
Subscriptions getting deleted want sql to email me who and when it happens. Does anybody have a t-sql for me to use?
Thanks! 😀
October 15, 2010 at 1:55 pm
Since a subscription is really just a sql server job, I'd just create a new job that executes a proc. the proc executes "SELECT job_id, [name] FROM msdb.dbo.sysjobs;" and store the result in some table. Then when the job executes again, it would compare what is stored in the table to what the query output looks like now, and if there is an entry missing, then send a db mail.
October 15, 2010 at 2:33 pm
Seems like a lot just for an email to find out who is deleting my report programmers subscriptions. Do you have a query that I can use? I get what your saying just wanted to know if you had it handy.
Thanks :w00t:
October 18, 2010 at 8:42 am
I'm not really sure that who/when a subscription was deleted is logged by default. You'll probably have to check on it yourself either by a job like was suggested by GetOffMyFoot, or you could use a very well filtered trace on the reportserver database. The subscription definitions are stored in the subscriptions table, you can join that to the catalog table so you can get some more readable names to go with your subscriptions.
To find out exactly what happens when a subscription is deleted, I'd get a test subscription set up and then start a trace delete the subscription and stop the trace. You'll then be able to review the collected records to see what information is available, set you filters appropriately and then restart your trace.
-Luke.
January 19, 2011 at 8:28 am
Could you not just use the site settings to disable subscription management for all users except those you can trust? i.e. yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply