Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchangerepltablepasswords

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSchangerepltablepasswords
(
	@server				sysname,
	@login				sysname,
	@password			sysname,
	@distribution_db	sysname
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @retcode			int,
			@proc				nvarchar(524),
			@server_id			int,
			@encrypted_password nvarchar(524)

	-- Security Check
	IF IS_SRVROLEMEMBER('sysadmin') <> 1
	BEGIN
		-- "Only members of the sysadmin fixed server role can perform this operation."
		RAISERROR(21089, 16, -1)
		RETURN 1
	END
	
	-- retrieve the server id for later use
	SELECT @server_id = srvid
		FROM [master].[dbo].sysservers
		WHERE UPPER(srvname) = UPPER(@server)
	
	/*
	 * Perform updates based on type of db
	 */
	BEGIN TRANSACTION tr_MSchange_repltable_password
	SAVE TRANSACTION tr_MSchange_repltable_password
	
	-- Distribution Database tables
	IF OBJECT_ID('MSlogreader_agents', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password
		
		EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSlogreader_agents
			SET publisher_password = @encrypted_password
			WHERE (publisher_id = @server_id
					OR @server = N'%')
				AND publisher_security_mode = 0
				AND publisher_login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'logreader agent', 'publisher', @server, @login) WITH LOG
	END

	IF OBJECT_ID('MSsnapshot_agents', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password
		
		EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSsnapshot_agents
			SET publisher_password = @encrypted_password
			WHERE (publisher_id = @server_id
					OR @server = N'%')
				AND publisher_security_mode = 0
				AND publisher_login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'snapshot agent', 'publisher', @server, @login) WITH LOG
	END
	
	IF OBJECT_ID('MSdistribution_agents', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password
		
		EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSdistribution_agents
			SET subscriber_password = @encrypted_password
			WHERE (subscriber_id = @server_id
					OR @server = N'%')
				AND subscriber_security_mode = 0
				AND subscriber_login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'distribution agent', 'subscriber', @server, @login) WITH LOG
	END

	IF OBJECT_ID('MSmerge_agents', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password
		
		EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSmerge_agents
			SET subscriber_password = @encrypted_password
			WHERE (subscriber_id = @server_id
					OR @server = N'%')
				AND subscriber_security_mode = 0
				AND subscriber_login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'merge agent', 'subscriber', @server, @login) WITH LOG

		UPDATE MSmerge_agents
			SET publisher_password = @encrypted_password
			WHERE (publisher_id = @server_id
					OR @server = N'%')
				AND publisher_security_mode = 0
				AND publisher_login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'merge agent', 'publisher', @server, @login) WITH LOG
	END

	IF OBJECT_ID('MSsubscriber_info', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password
		
		EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSsubscriber_info
			SET password = @encrypted_password
			WHERE (UPPER(subscriber) = UPPER(@server)
					OR @server = N'%')
				AND security_mode = 0
				AND login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'default', 'subscriber', @server, @login) WITH LOG
	END

	-- Subscription Database tables
	IF OBJECT_ID('MSsubscription_properties', 'U') IS NOT NULL
	BEGIN
		DECLARE @fpullsubexists bit

		SELECT @fpullsubexists = 0
		
		IF OBJECT_ID('MSreplication_subscriptions', 'U') IS NOT NULL
		BEGIN
			-- we will only update if there are any pull subscriptions
			IF EXISTS(SELECT *
						FROM MSreplication_subscriptions
						WHERE subscription_type != 0)
			BEGIN
				SELECT @fpullsubexists = 1
			END
		END

		IF OBJECT_ID('sysmergesubscriptions', 'U') IS NOT NULL
		BEGIN
			-- we will only update if there are any pull subscriptions
			IF EXISTS(SELECT *
						FROM sysmergesubscriptions
						WHERE subscription_type != 0)
			BEGIN
				SELECT @fpullsubexists = 1
			END
		END

		IF @fpullsubexists = 1
		BEGIN
			-- Encrypt the password
			SELECT @encrypted_password = @password
			
			EXEC @retcode = sys.sp_MSreplencrypt @encrypted_password OUTPUT
			IF @@ERROR <> 0 OR @retcode <> 0
				GOTO FAILURE
				
			UPDATE MSsubscription_properties
				SET publisher_password = @encrypted_password
				WHERE (UPPER(publisher) = UPPER(@server)
						OR @server = N'%')
					AND publisher_security_mode = 0
					AND publisher_login = @login
			IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'subscription', 'publisher', @server, @login) WITH LOG
			
			UPDATE MSsubscription_properties
				SET distributor_password = @encrypted_password
				WHERE (UPPER(distributor) = UPPER(@server)
						OR @server = N'%')
					AND distributor_security_mode = 0
					AND distributor_login = @login
			IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'subscription', 'distributor', @server, @login) WITH LOG

			
			-- UPDATE MSsubscription_properties
			-- 	SET internet_password = @encrypted_password
			-- 	WHERE internet_security_mode = 0
			-- 		AND internet_login = @login
			-- IF @@ERROR <> 0 GOTO FAILURE
			
		END
	END

	-- MSDB Database tables
	IF OBJECT_ID('MSdistpublishers', 'U') IS NOT NULL
	BEGIN
		-- Encrypt the password
		SELECT @encrypted_password = @password,
				@proc = QUOTENAME(@distribution_db) + '.sys.sp_MSreplencrypt'
		
		EXEC @retcode = @proc @encrypted_password OUTPUT
		IF @@ERROR <> 0 OR @retcode <> 0
			GOTO FAILURE
			
		UPDATE MSdistpublishers
			SET password = @encrypted_password
			WHERE (UPPER(name) = UPPER(@server)
					OR @server = N'%')
				AND security_mode = 0
				AND login = @login
		IF @@ROWCOUNT > 0 RAISERROR(21451, 10, -1, 'default', 'publisher', @server, @login) WITH LOG
	END

	COMMIT TRANSACTION tr_MSchange_repltable_password

	RETURN 0
FAILURE:
	ROLLBACK TRANSACTION tr_MSchange_repltable_password
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_changereplicationserverpasswords (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash