BUG: sp_droppublisher Does Not Clear 'pub' Server Option

• Microsoft SQL Server version 6.5

SYMPTOMS

The sp_droppublisher stored procedure, used to drop a publication server, does not properly clear the 'pub' server option from sysservers. This will prevent users from properly disabling a publication server on the screen "Enable Subscribing from these Servers."

WORKAROUND

In the master database of any server participating in replication, please replace the sp_droppublisher procedure by running the following script:

use master go if exists (select * from sysobjects where sysstat & 0xf = 4 and name =

'sp_droppublisher')
   drop procedure sp_droppublisher

   @publisher varchar (30),        /* publisher server name */
   @type varchar (5) = null     /* null or 'dist' */
        ) as

    declare @distaccount varchar(127)
    declare @proc varchar (255)
    declare @retcode int
    declare @privilege varchar (30)

    /*
    ** parameter check:  @publisher.
    ** check to make sure that the publisher exists, that the name isn't
    ** null, and that the name conforms to the rules for identifiers.
    */

    if @publisher is null
        begin
            raiserror (14043, 16, -1, 'the publisher')
            return (1)
        end

    execute @retcode = sp_validname @publisher

    if @retcode <> 0
   return (1)

    /*
    ** perform special logic if dropping a publisher for a distribution
    ** server.
    */
    if lower(@type) = 'dist'
      begin
       if not exists (select *
      from master..sysservers
                where srvname = @publisher
                and srvstatus & 16 <> 0)

       begin
      raiserror (14080, 11, -1)
           return (1)
       end

       execute @retcode = sp_serveroption @publisher, 'dpub', false
       if @@error <> 0 or @retcode <> 0 return (1)

            if exists (select * from master..sysremotelogins
          where remoteserverid = (select srvid from master..sysservers
          where srvname = @publisher)
          and remoteusername = 'sa'
          and suid = 1)    /* 'sa' */
       begin
          execute @retcode = sp_dropremotelogin @publisher, sa, sa
          if @@error <> 0 or @retcode <> 0 return (1)
       end

            if exists (select * from master..sysremotelogins
          where remoteserverid = (select srvid from master..sysservers
          where srvname = @publisher)
          and remoteusername = 'probe'
          and suid = 10)   /* 'probe' */
       begin
          execute @retcode = sp_dropremotelogin @publisher, probe, probe
          if @@error <> 0 or @retcode <> 0 return (1)
       end

       return (0)
   end

    /*
    ** make sure the server is defined as a 'publisher'.
    */
    if not exists (select *
                     from master..sysservers
                    where srvname = @publisher
                      and srvstatus & 2 <> 0)

        begin
            raiserror (14080, 11, -1)
            return (1)
        end

    /*
    ** turn off the server option to indicate that this is a publisher.
    */
    execute @retcode = sp_serveroption @publisher, 'pub', false
    if @@error <> 0 or @retcode <> 0 return (1)

    /*
    ** fetch the publisher's distributor account.
    */

    select @proc = rtrim(@publisher) + '.master..sp_helpdistributor '
    exec @retcode = @proc @account = @distaccount output
    if @@error <> 0 or @retcode <> 0
        begin
            raiserror (14071, 16, -1)
            return (1)
        end

    /*
    ** if @distaccount = 'localsystem' assume 'admin' privilege
    */
    if @distaccount = 'localsystem'
       return (0)

    /*
    ** if @distaccount has 'admin' privilege, do not revoke
    */
    execute @retcode = master.dbo.xp_logininfo @distaccount, 'all',
       @privilege = @privilege output
    if @@error <> 0 or @retcode <> 0 return (1)

    if @privilege = 'admin'
       return  (0)

    /*
    ** revoke replication privilege to the distributor nt account.
    */
    exec @retcode = master.dbo.xp_revokelogin @distaccount
    if @@error <> 0 or @retcode <> 0 return (1)

To clear the problem before applying the procedure, you can manually disable the 'pub' server option by executing the following statement on the subscription server:

use master go sp_serveroption <publication server>, 'pub', false go

Example:

use master go sp_serveroption AIKMAN, 'pub', false go

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

When using SQL Enterprise Manager to disable a publication server (via the screen "Enable Subscribing from these Servers"), the operation will appear to succeed and the checkbox indicating the publication server is disabled will be cleared. If you return to the same screen without exiting SQL Enterprise Manager, the checkbox will still be cleared. However, any attempt to re-enable the publication server will result in the following SQL-DMO error:

   Error 14704: [SQL Server] The server '<publication server>' is already
   listed as a publisher