Permissions on replicated proc

• JimmiDill

  SSC Enthusiast

  Points: 145

  More actions

  May 13, 2009 at 7:09 am

  #95486

  Hi,

  I want to replicate stored procedures to subscribers (transactional replication) but I also want the proc to be deployed with GRANT EXEC permissions.

  Trying to add 0x40000000 (Replicate permissions.) schema options via sp_addarticle but it appears that isn't valid for that replication type:

  "Msg 21222, Level 16, State 1, Procedure sp_MSaddschemaarticle, Line 138

  The @schema_option parameter for a procedure or function schema article can include only the options 0x0000000000000001 or 0x0000000000002000."

  But chances are I'm not calculating the bitmask properly (bitmaps not my strong point!) or this option is not available (more likely the message is fairly clear)

  Before I rule this out, does anyone know of a valid schema option for replicating permissions where the replication type is 'proc schema only'? Or will I need to use a post snapshot script?

  Thanks

  Jim

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  May 13, 2009 at 10:00 pm

  #994256

  I just created a publication with a single sproc in it. The script include sp_addarticle for that sproc with @schema_option = 0x0000000008000001.

  I changed this to @schema_option = 0x0000000048000001 and the article was successfully created.

• JimmiDill

  SSC Enthusiast

  Points: 145

  More actions

  May 14, 2009 at 2:49 am

  #994328

  Thanks,

  You're right, this works in 2K5. But not 2000 which I'm lumbered with 🙁

  I've just had a little moan about getting more of our databases moved over to 2K5 or 2K8...!

  Thanks anyway.. will start thinking about using a post snapshot script.

  Cheers,

  Jim