Change Report Owner

We had a person create over 200 report subscriptions and they are
showing up as the report onwer. No that this person has left and their
domain account has been deleted we have found that the SSRS 2005
report subscriptions are now failing.
How do you change the report subscriptions owner to someone that still
exists within the organisation? Or what else would be causing this
issue?
TIAOn Sep 5, 1:56 am, white...@.gmail.com wrote:
> We had a person create over 200 report subscriptions and they are
> showing up as the report onwer. No that this person has left and their
> domain account has been deleted we have found that the SSRS 2005
> report subscriptions are now failing.
> How do you change the report subscriptions owner to someone that still
> exists within the organisation? Or what else would be causing this
> issue?
> TIA
You are correct in assuming the subs are failing due to deletion of
the report owner's account. The same thing happened to us.
You will need to do one of two things: 1) have a current valid user
recreate all of the affected subs or 2) update the OwnerID value in
the Subscriptions table to a current valid user id.
What we did to eliminate this happening every time someone left was
create a generic "SubscriptionOwner" user account and amended the
stored procs (CreateSubscription And UpdateSubscription) that create
subscriptions so that the creating OwnerID value in the Subscriptions
table is replaced with that of the "SubscriptionOwner".|||On Sep 6, 2:55 am, toolman <t...@.infocision.com> wrote:
> On Sep 5, 1:56 am, white...@.gmail.com wrote:
> > We had a person create over 200 report subscriptions and they are
> > showing up as the report onwer. No that this person has left and their
> > domain account has been deleted we have found that the SSRS 2005
> > report subscriptions are now failing.
> > How do you change the report subscriptions owner to someone that still
> > exists within the organisation? Or what else would be causing this
> > issue?
> > TIA
> You are correct in assuming the subs are failing due to deletion of
> the report owner's account. The same thing happened to us.
> You will need to do one of two things: 1) have a current valid user
> recreate all of the affected subs or 2) update the OwnerID value in
> the Subscriptions table to a current valid user id.
> What we did to eliminate this happening every time someone left was
> create a generic "SubscriptionOwner" user account and amended the
> stored procs (CreateSubscription And UpdateSubscription) that create
> subscriptions so that the creating OwnerID value in the Subscriptions
> table is replaced with that of the "SubscriptionOwner".
Thanks for the advice.
However, I have changed the OwnerID in the Subscriptions table but
when I edit the subscription through report manager it still shows the
old username as "Owner:", but if performing a search through the
databse itself it doesn't show any where. I even tried renaming the
UserName field in the Users table, but the name still shows under
"Owner:" in Report Manager.
Any more help would be appreciated.

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
==================================== declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCHChange one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegroups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
====================================
declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCH
Change one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegr oups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================
> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================
> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>

Change object all object onwer

I have a script like below to change all the object owner from "xxxxx"
to "dbo".
====================================
declare @.name varchar(250)
declare @.olduser varchar(250)
declare @.newuser varchar(250)
declare @.sp_str varchar(250)
set @.olduser = 'xxxxx.'
set @.newuser = 'dbo'
declare cursor1 cursor for
select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
uid of 'xxxxx')
open cursor1
fetch next from cursor1 into @.name
WHILE @.@.FETCH_STATUS = 0
--while begin transaction
begin
set @.sp_str = @.olduser + @.name
exec sp_changeObjectOwner @.sp_str,@.newuser
fetch next from cursor1 into @.name
end
--commit transaction
close cursor1
deallocate cursor1
====================================
How if I want to change only a particular database, don't care what's
the existing object owner is, but just change all the object owner to
"xxxxx"?
Does anyone have good script to change that?
Or any way to modify the above script to acomplish that?
Peter CCHChange one line:
select sysobjects.name from sysobjects where sysobjects.xtype = 'u' and
sysobjects.uid NOT IN (SELECT sysusers.uid FROM sysusers WHERE sysusers.name
<> @.newuser)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1133099207.669540.204000@.g14g2000cwa.googlegroups.com...
>I have a script like below to change all the object owner from "xxxxx"
> to "dbo".
> ====================================
> declare @.name varchar(250)
> declare @.olduser varchar(250)
> declare @.newuser varchar(250)
> declare @.sp_str varchar(250)
> set @.olduser = 'xxxxx.'
> set @.newuser = 'dbo'
> declare cursor1 cursor for
> select name from sysobjects where xtype = 'u' and uid = 5 -- (5 - the
> uid of 'xxxxx')
> open cursor1
> fetch next from cursor1 into @.name
> WHILE @.@.FETCH_STATUS = 0
> --while begin transaction
> begin
> set @.sp_str = @.olduser + @.name
> exec sp_changeObjectOwner @.sp_str,@.newuser
> fetch next from cursor1 into @.name
> end
> --commit transaction
> close cursor1
> deallocate cursor1
> ====================================
> How if I want to change only a particular database, don't care what's
> the existing object owner is, but just change all the object owner to
> "xxxxx"?
> Does anyone have good script to change that?
> Or any way to modify the above script to acomplish that?
>
>
> Peter CCH
>