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 database between servers

I need to move a SQL 2005 database between 2 servers.
But the 2 servers have different path structure due to language difference.
One server has the databse installed under "c:\Program Files ..." and the
other under "C:\Programmer..." It is not possible to restore the database,
and present the error Directory lookup for the file" C:\Programmer\Microsoft
SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
The path was not fould.
How can I manually manage the backup and Restore to accomodate the different
path setup on the 2 servers?
I've used Enterprise Manager to export the original database and import it
to the new server. As I recall, I got an error popup and was given the
opportunity to change the location.
One approach I've used is to create the directory structure I wanted on the
new server. I then detached the database (in Enterprise Manager) and copied
the MDF file to where I want it. Then attach the database and change the
location of the MDF file. Verify it works and delete the old copy. You can
do the same for the LDF (log) file...
Regards,
Hank Arnold
"Bigalexx" <Bigalexx@.community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language
> difference. One server has the databse installed under "c:\Program Files
> ..." and the other under "C:\Programmer..." It is not possible to restore
> the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the
> operating system error The path was not fould.
> How can I manually manage the backup and Restore to accomodate the
> different path setup on the 2 servers?
>
|||You need to use the MOVE option for the backup command. This is documented in Books Online (BACKUP
DATABASE). This is also exposed in Management Studio, options page, "Restore As" column
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bigalexx" <Bigalexx@.community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language difference. One server has the
> databse installed under "c:\Program Files ..." and the other under "C:\Programmer..." It is not
> possible to restore the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
> The path was not fould.
> How can I manually manage the backup and Restore to accomodate the different path setup on the 2
> servers?
>