Skip navigation

Replicating Stored-Procedure Changes and Permissions

I added stored procedures as articles to my merge replication publications so that SQL Server would copy the stored procedures to the subscriber database. Although the publications' Snapshot Agent is set to begin delivery of the new stored-procedure articles to the subscriber at the first replication step, changes to the stored procedures on the publisher aren't being replicated to the subscriber. How can I ensure that these changes are made during replication?

After you create views, user-defined functions (UDFs), and stored-procedure definitions in a database, they appear as objects in the Create Publication Wizard's Specify Articles dialog box. When you replicate these objects, the initial snapshot applied at the subscriber replicates the object definitions, but subsequent changes to these object definitions aren't copied automatically to subscribers. To ensure that these changes can be easily propagated to the subscriber, create a separate publication for the stored-procedure articles, then reinitialize the subscribers when changes occur.

If you want to make custom changes such as setting permissions to stored-procedure, view, or UDF articles at the subscriber, in SQL Server 2000 you can specify a script containing the GRANT statements that the replication will automatically apply after the snapshot is delivered. For more information about running these scripts, see the "Executing Scripts Before and After the Snapshot is Applied" topic in SQL Server Books Online (BOL). In SQL Server 7.0, you have to apply the script manually after the snapshot is delivered.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.