Database moved - How to change subscriptions

I want to change a subscription that has been created by another user. I'am NOT an administrator of the server, but I've given me all Reporting-Services permissions (including systemadministrator). The logfile says that I have to be a member of the sysadmin-role to change subscriptions owned by another user.
Thanks
Benjamin

I found out, that the problem appears when I move the database from one machine to another. All works fine, but the owner of the SQL-Server-Agent Job seems to cause the problems. On the source-machine it is ASPNET and on destination-machine its SYSTEM, when I manually change it back to ASPNET I can edit/save the subscriptions.

So how do I set the owner of the SQL-Server Agent Jobs programaticly for all Jobs?
Or is my concept of moving the database via backup/restore not the best solution?

Thanks
Benjamin

|||

After you have moved the database to another machine, you could actually go ahead and just delete all the RS generated SQL Agent Jobs before you start up the report server windows service.

On startup of the RS windows service it will automatically recreate the agent jobs if they were deleted.

-- Robert

|||Restarting the Service creates the jobs, thats right, but they have still the wrong owner..
On both machines the owner is ASPNET if I create a new subscription (or look at a existing subscription on the test-server). Deleting the Agent-Job and starting the Report-Server Service creates the Agent-Jobs with System as owner!
Even on the test-machine(source) deleting the Agent-Job and restarting ReportingServices produces this error..
I've made a little stored procedure which changes the owners, after running it all works as expected..
Create PROCEDURE [dbo].[sp_AlterOwnerOfAgentJob] ( @.username nvarchar(128))
AS
BEGIN
SET NOCOUNT ON;
declare @.jobname nvarchar(128),@.servername nvarchar(128),@.UserID uniqueidentifier
DECLARE job_cursor CURSOR FOR select name from msdb.dbo.sysjobs
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @.jobname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @.job_name=@.jobname, @.owner_login_name=@.username
FETCH NEXT FROM job_cursor INTO @.jobname
END
CLOSE job_cursor
DEALLOCATE job_cursor
END
GO
exec sp_AlterOwnerOfAgentJob 'SERVERNAME\ASPNET'
GO

Is there any sp to list all jobs? That would make my sp work in further versions..
|||

Thanks for reporting this issue. We will look into this in more detail.

-- Robert

sql