create procedure sys.sp_MSrepl_dropsubscriber
(
	@subscriber			sysname,
	@reserved			nvarchar(50),
	@ignore_distributor	bit,
	@publisher			sysname,
	@noraise			int = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @db_name			sysname
    DECLARE @foundSubscription	int
    DECLARE @ClearSubStatus		int
    DECLARE @command			nvarchar(255)
    DECLARE @transpublishdb_bit	int
    DECLARE @mergepublishdb_bit	int
    DECLARE @distdb_bit	        int
    DECLARE @distributor		sysname
    DECLARE @distribdb			sysname
    DECLARE @distproc			nvarchar (300)
    DECLARE @retcode			int
    DECLARE @type				nvarchar(10)
    DECLARE @publisher_local	sysname

    SELECT @transpublishdb_bit	= 1
    SELECT @mergepublishdb_bit	= 4
    SELECT @distdb_bit			= 16
    SELECT @ClearSubStatus		= 1

	-- 'sysadmin' Security check was moved to sp_dropsubscriber.
	-- NOTE:  sp_MSrepl_dropsubscriber is also called directly by sp_dropsubscription.
	--        'db_owner' is sufficient authorization when called in this manner.
    /*
    ** Security Check: require sysadmin or DBO of publishing database
    */
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0) AND
        (ISNULL(IS_MEMBER('db_owner'),0) = 0)
    BEGIN
         RAISERROR(21050,16,-1)
         RETURN (1)
    END

    /*
    ** If @publisher is NULL, set @publisher_local to @@SERVERNAME
    ** otherwise, set @publisher_local to @publisher
    */
    IF @publisher IS NULL
    BEGIN
        select @publisher_local = publishingservername()
    END
    ELSE
    BEGIN
		/* For heterogeneous publisher, the current database must be the */
		/* distribution db for the publisher */
        select @publisher_local = @publisher

		IF NOT EXISTS
        (
        	SELECT	*
        	FROM	master.dbo.sysservers s,
        			msdb..MSdistpublishers m
			WHERE	UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)
			  AND	UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
			  AND	UPPER(m.distribution_db collate database_default) = UPPER(DB_NAME()) collate database_default
        )
        BEGIN
        	RETURN (1)
        END
    END

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure that the subscriber exists.
    */

    IF @subscriber IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_dropsubscriber')
		RETURN (1)
	END

    EXECUTE @retcode = sys.sp_validname @subscriber

    IF @retcode <> 0
    BEGIN
    	RETURN (1)
    END

    IF LOWER(@subscriber) = 'all'
    BEGIN
        DECLARE #hCdropsubscriber_all CURSOR LOCAL FAST_FORWARD FOR
		SELECT	srvname
		FROM	master.dbo.sysservers
		WHERE	srvstatus & 4 <> 0
        FOR READ ONLY

        OPEN #hCdropsubscriber_all
        FETCH #hCdropsubscriber_all INTO @subscriber

        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE @retcode  = sys.sp_dropsubscriber	@subscriber         = @subscriber,
							@publisher          = @publisher,
							@ignore_distributor = @ignore_distributor,
							@reserved           = @reserved
            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)

            FETCH #hCdropsubscriber_all INTO @subscriber
        end

        return(0)
    end


    /*
    ** There should be no subscription by the subscriber
    ** Open a cursor the published databases.
    **
    */
    IF @publisher IS NULL
    BEGIN
	DECLARE #hCdropsubscriber CURSOR LOCAL FAST_FORWARD FOR
	SELECT	name,
			N'tran'
	FROM	master.dbo.sysdatabases
	WHERE	(category & @transpublishdb_bit) <> 0
			and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
	UNION
	SELECT	name, N'merge' from master.dbo.sysdatabases
	WHERE	(category & @mergepublishdb_bit) <> 0
			and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
	FOR READ ONLY

	OPEN #hCdropsubscriber
	FETCH #hCdropsubscriber INTO @db_name, @type

	WHILE (@@fetch_status <> -1)
	BEGIN
            IF LOWER(@reserved) = N'drop_subscriptions'
            BEGIN
                if @type = N'tran'
                begin
                    SELECT @command = quotename(@db_name) + N'.sys.sp_dropsubscription'
                    EXECUTE @retcode  = @command
			@publication        = N'all',
			@article            = N'all',
			@subscriber         = @subscriber,
			@publisher          = @publisher,
			@ignore_distributor = @ignore_distributor
                end
                if @type = 'merge'
                begin
                    SELECT @command = quotename(@db_name) + N'.sys.sp_dropmergesubscription'
                    EXECUTE @retcode  = @command
                    	@publication = N'all',
                    	@subscriber = @subscriber,
                    	@subscription_type = N'both',
                    	@ignore_distributor = @ignore_distributor
                end
                IF @@ERROR <> 0 OR @retcode <> 0
                    RETURN (1)
            END

            -- Set @foundSubscription to not null so that sp_helpsubscription will not display
            -- result set
            select @foundSubscription = 0

            if @type = 'tran'
            begin
                SELECT @command = quotename(@db_name) + N'.sys.sp_helpsubscription'
                EXECUTE @retcode  = @command
						@publisher   = @publisher,
						@publication = N'%',
						@article     = N'%',
						@subscriber  = @subscriber,
						@found       = @foundSubscription  OUTPUT

                IF @@ERROR <> 0 OR @retcode <> 0
                    RETURN (1)
            end
            else
            begin
                SELECT @command = quotename(@db_name) + '.sys.sp_helpmergesubscription'
                EXECUTE @retcode  =	@command	@publication 		= N'%',
						@subscriber  		= @subscriber,
						@subscription_type	= N'both',
						@found	     		= @foundSubscription  OUTPUT

                IF @@ERROR <> 0 OR @retcode <> 0
                    RETURN (1)
            end


            IF @foundSubscription <> 0
            BEGIN

                IF ISNULL(@noraise, 0) = 0
                BEGIN
                    RAISERROR ( 14144,  16, -1, @subscriber, @db_name)
                    RETURN (1)
                END
			
                -- Return without error if @noraise = 1
                -- Used when called from sp_dropsubscription to drop
                -- subscriber when all subscriptions have been dropped.
                RETURN (0)
            END		

            FETCH #hCdropsubscriber INTO @db_name, @type

        END
    END
    ELSE
    BEGIN
        IF LOWER(@reserved) = N'drop_subscriptions'
        BEGIN
            -- For heterogeneous publishers execute sp_dropsubscription for the publisher and
            -- subscriber in the current database.
            -- @ignore_distributor should always be set to 0 since this will always execute
            -- on the Oracle distributor.
            SELECT @command = N'sys.sp_dropsubscription'
            EXECUTE @retcode  = @command
		@publication        = N'all',
		@article            = N'all',
		@subscriber         = @subscriber,
		@publisher          = @publisher,
		@ignore_distributor = 0

            IF @@ERROR <> 0 OR @retcode <> 0
		RETURN (1)
	END

	SELECT @foundSubscription = 0
	SELECT @db_name = DB_NAME()

	SELECT @command = N'sys.sp_helpsubscription'
	EXECUTE @retcode  = @command
		@publisher   = @publisher,
		@publication = N'%',
		@article     = N'%',
		@subscriber  = @subscriber,
		@found       = @foundSubscription  OUTPUT

	IF @foundSubscription <> 0
	BEGIN

                IF ISNULL(@noraise, 0) = 0
                BEGIN
                    RAISERROR ( 14144,  16, -1, @subscriber, @db_name)
                    RETURN (1)
                END
			
                -- Return without error if @noraise = 1
                -- Used when called from sp_dropsubscription to drop
                -- subscriber when all subscriptions have been dropped.
                RETURN (0)
	END		

	IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)

    END

    /*
    ** Drop the subsubscriber_info in the distribution database
    */
    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin


        /*
        ** Get distribution server information for remote RPC
        ** agent verification.
        */
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher  = @publisher,
															@rpcsrvname = @distributor OUTPUT,
															@distribdb  = @distribdb   OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            BEGIN
            RAISERROR (14071, 16, -1)
               RETURN (1)
        END

        /*
        ** Delete information from MSsubscriber_info in the distribution db
        */
        if @distribdb is not null
        begin
            SELECT @distproc =	QUOTENAME(RTRIM(@distributor)) + '.' +
            					QUOTENAME(RTRIM(@distribdb)) +
            					'.dbo.sp_MSdrop_subscriber_info'
            					
            EXEC @retcode = @distproc
							@publisher  = @publisher_local,
							@subscriber = @subscriber

            IF @@error <> 0 OR @retcode <> 0
                BEGIN
                RAISERROR (14042, 16, -1)
                RETURN (1)
            END
        end
    end

    -- We have already validated the existence of the server at this point
    SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default

	IF @distribdb IS NOT NULL AND UPPER(@@SERVERNAME) = UPPER(@distributor)
	BEGIN
	
		-- Determine whether SUBSCRIBER bit in SRVSTATUS can be cleared
		EXECUTE @ClearSubStatus = sys.sp_MSDropCanClearSubscriberStatus @publisher_local,
											@distributor,
											@subscriber,
											@distribdb
	END

	IF @ClearSubStatus = 1
	BEGIN
		-- Turn off the subscriber server option(s)
		SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default
		if (@subscriber is not null)
			EXEC @retcode = sys.sp_MSdrop_subserver @subscriber = @subscriber
		IF @@error <> 0 OR @retcode <> 0
		BEGIN
			RETURN (1)
		END
	END

    RAISERROR (14062, 10, -1)
END