sql server replication: 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

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
[quoted text, click to view]

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


[quoted text, click to view]