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...
>
>

Copying dBs

I am running SQL 2000 (using replication) & Win
2000.
We are getting a new production sever and would like to
know the best practice for moving all the SQL data from
the Old Server to the New Server. There are a few
questions the concern me.
- Will I have to setup replication on the new server?
- Will all the dB defaults be preserved?
Larry,
if the server names are the same, and you have backups of all databases, then you should be able torestore them on the new production server. If you're doing transactional replication, then make sure the distribution database is synchronized with the prod
uction database. If the server names are different, then I'd script out the publications and recreate on the new server. Provided no subscribers are allowed to do any data changes during this time, you should be able to add the subscriptions without apply
ing the data snapshot (@.sync_type ='none').
HTH,
Paul Ibison
|||In the long run you would be best to drop the subscriptions and publications
on the current production server. Back it up and restore it to the new
publisher.
Recreate the publications from scripts and then do create a nosync
subscription to your existing subscriber.
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:238c01c427b5$7a2e6040$a501280a@.phx.gbl...
> I am running SQL 2000 (using replication) & Win
> 2000.
> We are getting a new production sever and would like to
> know the best practice for moving all the SQL data from
> the Old Server to the New Server. There are a few
> questions the concern me.
> - Will I have to setup replication on the new server?
> - Will all the dB defaults be preserved?
>

copying databases to server with same name/IP

I need to copy databases with transactional replication to another box and the new server will be have the exact same name and IP address as the old server. I am planning on copying over complete backups of the master, msdb, model, distribution and userDB
databases to the new box and I was going to do a restore of each, then try to set up the replication again. Is there any way to keep the replication intact so I don't have to set it up again? Is there any particular order in which the databases have to b
e retored?
Try this order
master, msdb, distribution, publication databases. Restore the publication databases with the keep replication switch.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks for the response. I am not so hot with replication - just to clarify - by "publication databases" you mean the user databases right? The ones being replicated? Thanks again.
|||Yes
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

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