Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_changereplicationserverpasswords

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_changereplicationserverpasswords
(
	@login_type		tinyint,		-- 0: SQL Authenticated Logins or Linked Servers 1: Windows Authenticated Logins
	@login			nvarchar(257),
	@password		sysname,
	@server			sysname = N'%'
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @FIsPublisher	bit,
			@FIsDistributor	bit
			
	DECLARE @retcode			int,
			@command			nvarchar(4000),
			@database			sysname,
			@credential 		sysname,
			@identity			sysname,
			@publisher			sysname,
			@publisher_type		sysname,
			@distribution_db	sysname

	SELECT @FIsPublisher = 0,
			@FIsDistributor	= 0
			
	SELECT	@publisher_type = N'MSSQLSERVER',
			@server         = ISNULL(@server, N'%'),
			@password       = ISNULL(@password, N'')

	-- 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

	IF LOWER(DB_NAME()) != N'master'
	BEGIN
		-- "sp_changereplicationserverpasswords can only be executed in the master database."
		RAISERROR (21482, 16, -1, 'sp_changereplicationserverpasswords', 'master')
		RETURN 1
	END

	IF RTRIM(ISNULL(@login, N'')) = N''
	BEGIN
		-- Invalid value given for parameter @login.
		RAISERROR(21456, 16, -1, '@login')
		RETURN 1
	END

	IF RTRIM(ISNULL(@password, N'')) = N''
	BEGIN
		-- Invalid value given for parameter @password.
		RAISERROR(21456, 16, -1, '@password')
		RETURN 1
	END
	
	-- check for tran (category&1) and merge (category&4) published dbs
	IF EXISTS(SELECT name
				FROM master.sys.sysdatabases
				WHERE category & 1 = 1)
		OR
		EXISTS(SELECT name
				FROM master.sys.sysdatabases
				WHERE category & 4 = 4)
	BEGIN
		SELECT @FIsPublisher = 1
	END

	-- check for dist server or distdb
	IF EXISTS(SELECT *
				FROM master.dbo.sysservers	
				WHERE srvstatus & 8 <> 0)
		OR
		EXISTS (SELECT name
				FROM master.sys.sysdatabases
				WHERE category & 16 = 16)
	BEGIN
		SELECT @FIsDistributor = 1

		SELECT @distribution_db = name
			FROM master.sys.sysdatabases
			WHERE category & 16 = 16
	END

	/**********************************************************
	 *     CHANGES TO BE DONE OUTSIDE OF A TRANSACTION
	 **********************************************************/
	
	-- these steps must be done outside of the transaction
	-- block because they deal with linked server logins and
	-- can NOT be done within any transaction boundaries...
	IF @login_type = 0
	BEGIN	
		-- if we are at a distributor srv or at a publisher and
		-- if this is the dist admin link login name then
		-- we will attempt to update the dist link password
		IF (@FIsDistributor = 1
				OR @FIsPublisher = 1)
			AND @login = N'distributor_admin'
		BEGIN
			EXEC @retcode = sys.sp_changedistributor_password @password = @password
			IF @retcode <> 0 or @@ERROR <> 0
				RETURN 1

			RAISERROR(21451, 10, -1, 'distributor', 'admin', 'admin link', 'distributor_admin') WITH LOG
		END
		
		-- if we are at a distributor server then lets check to see if
		-- we have to update any hetero linked server logins in this run
		IF @FIsDistributor = 1
			AND OBJECT_ID('msdb..MSdistpublishers', 'U') IS NOT NULL
		BEGIN
			DECLARE cursorDistPublishers CURSOR LOCAL FAST_FORWARD FOR
				SELECT name
					FROM msdb..MSdistpublishers
					WHERE (UPPER(name) = UPPER(@server)
							OR @server = N'%')
						AND security_mode = 0
			OPEN cursorDistPublishers

			FETCH cursorDistPublishers INTO @publisher
			WHILE @@FETCH_STATUS <> -1
			BEGIN
				EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher      = @publisher,
																	@publisher_type = @publisher_type OUTPUT
				IF @retcode <> 0
					RETURN 1

				-- if this is a hetero publisher then we should
				-- attempt to update the remote login passwords
				IF @publisher_type != N'MSSQLSERVER'
				BEGIN
					EXEC @retcode = sys.sp_MSchangerepllinkedsrvrpassword	@server       = @publisher,
																			@remote_login = @login,
																			@new_password = @password,
																			@server_type  = @publisher_type
					IF @retcode <> 0 or @@ERROR <> 0
			        	RETURN 1
				END

				FETCH cursorDistPublishers INTO @publisher
			END

			CLOSE cursorDistPublishers
			DEALLOCATE cursorDistPublishers
		END	

		-- this is done for every database but, the current procedure
		-- sp_MSchangepublicationlinkpasswords being called performs a
		-- no-op for dbs that are not replication subscription databases...
		DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
			SELECT DISTINCT name
				FROM master..sysdatabases
			where databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
		OPEN cursorDatabases

		FETCH cursorDatabases INTO @database
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			-- set the database name in front of each sp call
			SELECT @command = QUOTENAME(@database) + N'.sys.sp_MSchangepublicationlinkpasswords'
			
			EXEC @retcode = @command @server = @server,
										@remote_login = @login,
										@new_password = @password
			IF @retcode <> 0 or @@ERROR <> 0
				GOTO UNDO
			
			FETCH cursorDatabases INTO @database
		END

		CLOSE cursorDatabases
		DEALLOCATE cursorDatabases
	END

	/**********************************************************
	 *       CHANGES TO BE DONE WITHIN A TRANSACTION
	 **********************************************************/
	
	BEGIN TRANSACTION tr_change_repl_password
	SAVE TRANSACTION tr_change_repl_password
	
	-- SQL Authenticated login password change
	IF @login_type = 0
	BEGIN
		/*
		 * Perform all per database updates first
		 */
		DECLARE cursorDatabases CURSOR LOCAL FAST_FORWARD FOR
			SELECT DISTINCT name
				FROM master..sysdatabases
			where databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
		OPEN cursorDatabases

		FETCH cursorDatabases INTO @database
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			-- set the database name in front of each sp call
			SELECT @command = QUOTENAME(@database) + N'.sys.sp_MSchangerepltablepasswords'
			
			EXEC @retcode = @command @server = @server,
										@login = @login,
										@password = @password,
										@distribution_db = @distribution_db
			IF @retcode <> 0 or @@ERROR <> 0
				GOTO UNDO
			
			FETCH cursorDatabases INTO @database
		END

		CLOSE cursorDatabases
		DEALLOCATE cursorDatabases
	END
	-- Windows Authenticated login password change
	ELSE IF @login_type = 1
	BEGIN
		IF @server IS NOT NULL
			AND @server != N'%'
		BEGIN
			-- Use of parameter(s) @server is invalid when parameter @login_type is set to 1.
			RAISERROR(22539, 16, -1, '@server', '@login_type', '1')
			GOTO UNDO
		END
		
		/*
		 * Perform all credential updates based on MSDB data
		 *
		 * NOTE: We should ONLY allow the change of
		 *		 REPLICATION credential passwords where
		 *		 the login matches the credential_identity
		 *			
		 *       Also, we upper the login and credential_identity
		 *       since windows accounts are case-insensitive...
		 */
		DECLARE cursorCredentials CURSOR LOCAL FAST_FORWARD FOR
			SELECT sc.name,
					sc.credential_identity
				FROM msdb.dbo.sysjobs_view as sjv
					JOIN msdb.dbo.sysjobsteps as sjs
						ON sjv.job_id = sjs.job_id
					JOIN msdb.dbo.sysproxies sp
						ON sjs.proxy_id = sp.proxy_id
					JOIN sys.credentials sc
						ON sc.credential_id = sp.credential_id
				WHERE sjv.master_server = 0
					AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
					AND ((sjv.category_id = 10
							AND sjs.subsystem = N'Distribution')
						OR (sjv.category_id = 14
							AND sjs.subsystem = N'Merge')
						OR (sjv.category_id = 13
							AND sjs.subsystem = N'LogReader')
						OR (sjv.category_id = 15
							AND sjs.subsystem =  N'Snapshot')
						OR (sjv.category_id = 19
							AND sjs.subsystem = N'QueueReader'))
					AND UPPER(sc.credential_identity) = UPPER(@login)
					
		OPEN cursorCredentials

		FETCH cursorCredentials INTO @credential, @identity
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			-- change the credential password
			SELECT @command = 'ALTER CREDENTIAL ' + QUOTENAME(@credential) +
								' WITH IDENTITY = ' + QUOTENAME(@identity, '''') +
								', SECRET = ' + QUOTENAME(@password, '''')
			
			EXEC(@command)
			IF @retcode <> 0 or @@ERROR <> 0
				GOTO UNDO

			RAISERROR(21451, 10, -1, 'job', 'credential', @credential, @identity) WITH LOG
			
			FETCH cursorCredentials INTO  @credential, @identity
		END

		CLOSE cursorCredentials
		DEALLOCATE cursorCredentials
	END
	ELSE
	BEGIN
		-- Invalid value given for parameter @login_type.
		RAISERROR(21456, 16, -1, '@login_type')
		GOTO UNDO
	END
	
	COMMIT TRANSACTION tr_change_repl_password

	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_change_repl_password
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
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