Correction

I forgot to mention that I was refering to merge replication
Shmuel
The 'disturbed' batch will be redone and subsequently further batches will
be done.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you,
Shmuel
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ezk0eMZKFHA.2748@.TK2MSFTNGP09.phx.gbl...
> The 'disturbed' batch will be redone and subsequently further batches will
> be done.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

Copy SQL2k5 publisher/distributor to new server

I have seen a few threads asking about copying a complete server,
including all of replication (merge), to a new server. I notice that
the answers state that it should be possible as long as the server
name remains the same. We are in this situation today, and do not
have the luxury of recreating all of the subscribers, nor do we have
control over when they sync -( merge replication), so we want to shut
merging down, move to a new server, and restart, without our
subscribers missing a beat.
Has anyone actually accomplished this? Any gotcha's?
I appreciate any shared experiences.
Thanks,
john g.
In my experience provided you follow the guidelines for "Strategies for
Backing Up and Restoring Merge Replication" in BOL theere should be no
issues. The only extra thing I can think of is to be sure to do it all
within the retention period (history and transaction and subscription
deactivation).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com