Replicating Stored Procedure permissions issue

I'm having a problem replicating stored procedure permssions accross two sql server 2005 servers, using transactional replication. When I replicate tables, it's easy enough to send the permissions over from the subscriber since there is an option under the articles properties for "Copy permissions." I can't seem to find any such option for SPs, UDFs or views. Am I missing something?

So the other idea I had was to create a script to grant the proper permissions, and run it using sp_addexecscript to my publication. Currently I have two one-way transactional publications. One publication for all my tables, and another for SPs, UDFs and views. I can run sp_addexecscript on the publication containing all my tables with out a problem, but when I run it against the publication containing my SPs, UDFs and views I get the following error:

Msg 21332, Level 16, State 1, Procedure sp_MSrepl_addscriptexec, Line 57
Failed to retrieve information about the publication : SP_UDF_Views_transactional. Check the name again.

I'm not sure why I'm getting this message. If anyone can help me out, I'd greatly appreciate it.

sp_addexecscript must be some user-defined stored procedure, I've never heard of it so I don't know what would cause be the problem. However the workaround would be to use parameter @.post_snapshot_script in sp_addpublication. Supply your permissions TSQL file here, it will automatically be applied at the subscriber after the snapshot is applied.

|||

While it is unfortunate that the option for replicating permissions with SPs\VWs\UDFs is not exposed in the UI (please file a bug at the Microsoft Connect site), you can enable the schema option (0x40000000) for replicating permissions via sp_addarticle.

Hope that helps,

-Raymond

|||Greg,

Thanks for the help, I will give that a try. Also, to clarify, I mistyped in my first post what command I'm using. The command is sp_addscriptexec. Sorry about the confussion.|||Excellent, both methods worked great! Thanks for the help!|||On the first test of altering the schema option, everything worked fine, but now I'm running into errors.

My original @.schema_option value was 0x0000000008000001, I changed it to 0x0000000048000001 to enable permission replication (0x40000000). Once I changed that value it tries to add "WITH EXECUTE AS CALLER" when it replicates the SPs. Then I get the following errors:

Error messages:
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'AS'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Must declare the scalar variable "@.sServer". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@.iType". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137

Once I change the schema option back to 0x0000000008000001, replication works, but without permissions...
|||Can you post the schema script for the problematic stored procedure generated by the snapshot agent? Thanks.|||

Just to rule out the obvious, can you post the schema option value returned by running sp_helparticle at the publisher as well? Thanks much.

-Raymond

|||Raymond,

Sorry to not get back to you sooner. I was out of the office for a few days. Here is the script I'm using to add the article (if this isn't what you wanted, please let me know).

exec sp_addarticle @.publication = N'dismal_v8_SP_UDF_Views_transactional', @.article = N'ct_getContentItem', @.source_owner = N'dbo', @.source_object = N'ct_getContentItem', @.type = N'proc schema only', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000048000001, @.destination_table = N'ct_getContentItem', @.destination_owner = N'dbo', @.status = 16

Also, the schema_option value returned by sp_helparticle: 0x0000000048000001

It's weird, I'm getting this error on nearly every stored procedure in my database. If I right click, modify, on the stored procedure and then hit execute without making any changes to the SP, this error goes away. Unfotunetly we have about 20 databases that will need to replicate 100 or so stored procedures, so I don't think "recompiling" the stored procedure is an acceptable solution. Let me know what you think. Thanks!|||

Are you getting the error when the initial snapshot is delivered or after you have changed your stored procedures at the publisher and have the "alter procedure" replicated to the subscriber (ddl replication). Not all schema options are honored during ddl replication. Ddl replication also doesn't handle the use of " for delimiting string literals.

If your subscribers are SQL2005 servers, they should have no trouble handling the "execute as" syntax so the problem that you are experiencing may be due to something else. It would be great if you can post the create (alter?) procedure statement that is executed at the subcriber, you should be able to get that from SQL Profiler.

Modifying text objects such as stored procedures by right clicking on the object can modify the object header in unexpected ways, you may want to use direct query to modifying your stored procedures instead.

-Raymond

|||Raymond,

I'm getting the error when the initial snapshot is delivered.

I will try to post the create procedure statement that is executed at the subscriver later today or tomorrow. I've since recreated the publication without the schema option to copy permissions in order to test other items.