Post reply

SOS! EXEC permissions lost on stored procedures while using snapshot replication

  • September 14, 2004 at 3:57 am

    #86446

    Hello everybody!

    I am using SQL Server 2000 and I replicate a database using snapshot replication (Enterprise Manager). On the publisher side, all my stored procedures have been granted EXEC permissions. Once the replication occurs, I loose all the EXEC permissions on the stored procedures on the subscriber side.

    Anyone has an idea?

    Thank you in advance for your help.

    Gabriel

  • September 14, 2004 at 6:09 am

    #522932

    I take it that your publication includes the stored procedures, not just the tables?

    The quickest way of reassigning pemissions is to assign permissions to a role then use TSQL Script to grant exec permissions to the role for your stored procs.

    DECLARE @sProc VARCHAR(50)

    SET @sProc=''

    WHILE @sProc IS NOT NULL

       BEGIN

         SELECT @sProc = MIN(Name)

         FROM dbo.SysObjects

         WHERE Name> @sProc AND Type='P'

         IF @sProc IS NOT NULL

             EXEC('GRANT EXEC ON ' + @sProc + ' TO myRole')

       END

    GO

    LinkedIn Profile

  • September 15, 2004 at 6:19 am

    #523136

      Thanks David! that worked!

    Cheers

    Gabriel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply