February 16, 2006 at 5:22 pm
I would like the snapshot generation to make dbo the owner of the sp_MSins,sp_MSdel etc stored procedures in the sch files. Is there any way to do this? We need to do this because the user executing the script on the subscriber is in the database owner group but is not THE database owner, so these stored procedures as created with him as the owner rather than dbo
February 16, 2006 at 8:54 pm
modify the publication using enterprise manager, go to each articles and modify the owner there for snapshot, then reinitialize the subscriptions so you can reflect the changes by generating a new snapshot
you can avoid modifying by specifying the owner name during first creation of publication (you just need to browse through each article)
HTH
February 16, 2006 at 9:13 pm
Thanks Jen,
I don't quite follow - On the publication properties, Articles tab the Owner is shown as dbo but the generated sch file looks like this
drop procedure [sp_MSins_tblActiveItem]
go
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSins_tblActiveItem') drop proc [sp_MSins_tblActiveItem]
go
create procedure [sp_MSins_tblActiveItem]
I want it to look like this:
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSins_tblActiveItem') drop proc dbo.[sp_MSins_tblActiveItem]
go
create procedure dbo.[sp_MSins_tblActiveItem]
Simon
February 16, 2006 at 10:31 pm
by right you should be able to modify this
is the script grayed out? does your account have SA privilege?
have you re-run the snapshot?
February 16, 2006 at 10:43 pm
I am a SysAdmin but can't even see the script from Enterprise Manager (SQL 2000)- I could modify the sch file directly but then would have to do this every time we generated the snapshot. I was hoping there was an option somewhere to avoid doing this.
February 16, 2006 at 10:55 pm
can you post the steps? there might be something missing...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply