Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSupgrade_subdb_security_metadata

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSupgrade_subdb_security_metadata
AS
BEGIN
	DECLARE @retcode					int,
			@job_id						uniqueidentifier,
			@job_step_id				int,
			@job_step_uid				uniqueidentifier,
			@command_line				nvarchar(3200),
			@distributor				sysname,
			@distributor_security_mode	int,
			@distributor_login			sysname,
			@distributor_password		nvarchar(524),
			@publisher					sysname,
			@publisher_db				sysname,
			@publication				sysname,
			@publisher_security_mode	int,
			@publisher_login			sysname,
			@publisher_password			nvarchar(524),
			@junk_password				nvarchar(524)
	
	-- setup a junk_password to be used for security_mode 1	
	SELECT @junk_password = NEWID()

	EXEC @retcode = sys.sp_MSsafe_repl_encrypt @junk_password OUTPUT
	IF @@ERROR <> 0 OR  @retcode <> 0
		RETURN 1
	
	BEGIN TRANSACTION tr_upgrade_subdb
	SAVE TRANSACTION tr_upgrade_subdb
		
	-- PULL DISTRIBUTION AGENTS
	IF OBJECT_ID(N'MSreplication_subscriptions', N'U') IS NOT NULL
		AND OBJECT_ID(N'MSsubscription_properties', N'U') IS NOT NULL
	BEGIN	
		/*
		 * Here we loop through each distribution agent to update the
		 * replication meta-data and the jobstep command-line...
		 */
		DECLARE cursorDistributionAgents CURSOR LOCAL FAST_FORWARD FOR
			SELECT msrs.publisher,
					msrs.publisher_db,
					msrs.publication,
					sjs.job_id,
					sjs.step_id,
					sjs.step_uid,
					sjs.command
				FROM MSreplication_subscriptions msrs
					LEFT JOIN msdb.dbo.sysjobs_view sjv
						ON sjv.job_id = msrs.agent_id
					LEFT JOIN msdb.dbo.sysjobsteps sjs
						ON sjv.job_id = sjs.job_id
				WHERE sjv.category_id = 10
					AND sjs.subsystem = N'Distribution'
					
		OPEN cursorDistributionAgents

		FETCH cursorDistributionAgents into @publisher, @publisher_db, @publication, @job_id, @job_step_id, @job_step_uid, @command_line
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			SELECT @distributor_security_mode = NULL,
					@distributor_login = NULL,
					@distributor_password = NULL
				
			/*
			 *  Retrieve command-line distributor login info
			 */

			SELECT @distributor_security_mode = CAST(arguments as int)
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorsecuritymode'

			SELECT @distributor_login = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorlogin'

			SELECT @distributor_password = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorencryptedpassword'

			IF @distributor_password IS NULL
			BEGIN
				SELECT @distributor_password = arguments
					FROM sys.fn_getparametervalues(@command_line)
					WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorpassword'
				
				EXEC @retcode = sys.sp_MSsafe_repl_encrypt @distributor_password OUTPUT
				IF @@ERROR <> 0 OR  @retcode <> 0
					GOTO UNDO
			END

			/*
			 * Set the actual values to be used in the meta-data update
			 */
			
			-- if security_mode is specified on the cmd then use the values
			IF @distributor_security_mode IS NULL
			BEGIN
				-- if the security_mode is null and login is provided set sec to 0
				IF @distributor_login IS NOT NULL
				BEGIN
					SELECT @distributor_security_mode = 0
				END
				-- else use whatever is in the table since it may have been upgraded already
				ELSE
				BEGIN					
					SELECT @distributor_security_mode = distributor_security_mode,
							@distributor_login = distributor_login,
							@distributor_password = distributor_password
					FROM MSsubscription_properties
					WHERE UPPER(publisher) = UPPER(@publisher)
						AND publisher_db = @publisher_db
						AND publication = @publication
				END
			END
			
			-- at this point if security_mode is NULL or 1 set the values
			IF @distributor_security_mode IS NULL
				OR @distributor_security_mode = 1
			BEGIN
				SELECT @distributor_security_mode = 1,
						@distributor_login = N'',
						@distributor_password = @junk_password
			END
			
			/*
			 * Remove/Update command-line parameters
			 */
		
			-- remove the distributor login parameters from the command-line
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorSecurityMode')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')

			-- remove the subscriber login parameters from the command-line
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberLogin')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberPassword')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberEncryptedPassword')
		
			-- ensure that the SubscriberSecurityMode on command-line is 1
			SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'SubscriberSecurityMode', N'1')

			/*
			 * Update meta-data table and jobstep command-line
			 */
			
			UPDATE MSsubscription_properties
				SET distributor_security_mode = @distributor_security_mode,
					distributor_login = @distributor_login,
					distributor_password = @distributor_password,
					job_step_uid = @job_step_uid
                WHERE UPPER(publisher) = UPPER(@publisher)
                    AND publisher_db = @publisher_db
                    AND (publication = @publication
                    	OR UPPER(publication) = N'ALL')

			UPDATE msdb.dbo.sysjobsteps
				SET command = @command_line,
					database_name = DB_NAME()
				WHERE job_id = @job_id
					AND (step_uid = @job_step_uid
						OR step_id = @job_step_id)

			FETCH cursorDistributionAgents into @publisher, @publisher_db, @publication, @job_id, @job_step_id, @job_step_uid, @command_line
		END
		
		CLOSE cursorDistributionAgents
		DEALLOCATE cursorDistributionAgents
	END

	-- PULL MERGE
	IF OBJECT_ID(N'MSmerge_replinfo', N'U') IS NOT NULL
		AND OBJECT_ID(N'sysmergesubscriptions', N'U') IS NOT NULL
		AND OBJECT_ID(N'sysmergepublications', N'U') IS NOT NULL
		AND OBJECT_ID(N'MSsubscription_properties', N'U') IS NOT NULL
	BEGIN		
		/*
		 * Here we loop through each merge agent to update the
		 * replication meta-data and the jobstep command-line...
		 */
		DECLARE cursorMergeAgents CURSOR LOCAL FAST_FORWARD FOR
			SELECT smp.publisher,
					smp.publisher_db,
					smp.name,
					sjs.job_id,
					sjs.step_id,
					sjs.step_uid,
					sjs.command
				FROM MSmerge_replinfo msmr
					JOIN sysmergesubscriptions sms
						ON msmr.repid = sms.subid
					JOIN sysmergepublications smp
						ON sms.pubid = smp.pubid
					LEFT JOIN msdb.dbo.sysjobs_view sjv
						ON sjv.job_id = msmr.merge_jobid
					LEFT JOIN msdb.dbo.sysjobsteps sjs
						ON sjv.job_id = sjs.job_id
				WHERE sjv.category_id = 14
					AND sjs.subsystem = N'Merge'

		OPEN cursorMergeAgents

		FETCH cursorMergeAgents into @publisher, @publisher_db, @publication, @job_id, @job_step_id, @job_step_uid, @command_line
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			SELECT @distributor_security_mode = NULL,
					@distributor_login = NULL,
					@distributor_password = NULL,
					@publisher_security_mode = NULL,
					@publisher_login = NULL,
					@publisher_password = NULL
				
			/*
			 *  Retrieve command-line distributor login info
			 */
			
			SELECT @distributor_security_mode = CAST(arguments as int)
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorsecuritymode'

			SELECT @distributor_login = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorlogin'

			SELECT @distributor_password = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorencryptedpassword'

			IF @distributor_password IS NULL
			BEGIN
				SELECT @distributor_password = arguments
					FROM sys.fn_getparametervalues(@command_line)
					WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'distributorpassword'
				
				EXEC @retcode = sys.sp_MSsafe_repl_encrypt @distributor_password OUTPUT
				IF @@ERROR <> 0 OR  @retcode <> 0
					GOTO UNDO
			END

			SELECT @publisher_security_mode = CAST(arguments as int)
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publishersecuritymode'

			SELECT @publisher_login = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherlogin'

			SELECT @publisher_password = arguments
				FROM sys.fn_getparametervalues(@command_line)
				WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherencryptedpassword'

			IF @publisher_password IS NULL
			BEGIN
				SELECT @publisher_password = arguments
					FROM sys.fn_getparametervalues(@command_line)
					WHERE LOWER(parameters collate SQL_Latin1_General_CP1_CS_AS) = N'publisherpassword'
				
				EXEC @retcode = sys.sp_MSsafe_repl_encrypt @publisher_password OUTPUT
				IF @@ERROR <> 0 OR  @retcode <> 0
					GOTO UNDO
			END

			/*
			 * Set the actual values to be used in the meta-data update
			 */
			
			-- if security_mode is specified on the cmd then use the values
			IF @distributor_security_mode IS NULL
			BEGIN
				-- if the security_mode is null and login is provided set sec to 0
				IF @distributor_login IS NOT NULL
				BEGIN
					SELECT @distributor_security_mode = 0
				END
				-- else use whatever is in the table since it may have been upgraded already
				ELSE
				BEGIN					
					SELECT @distributor_security_mode = distributor_security_mode,
							@distributor_login = distributor_login,
							@distributor_password = distributor_password
					FROM MSsubscription_properties
					WHERE UPPER(publisher) = UPPER(@publisher)
						AND publisher_db = @publisher_db
						AND publication = @publication
				END
			END
			
			-- at this point if security_mode is NULL or 1 set the values
			IF @distributor_security_mode IS NULL
				OR @distributor_security_mode = 1
			BEGIN
				SELECT @distributor_security_mode = 1,
						@distributor_login = N'',
						@distributor_password = @junk_password
			END
			
			-- if security_mode is specified on the cmd then use the values
			IF @publisher_security_mode IS NULL
			BEGIN
				-- if the security_mode is null and login is provided set sec to 0
				IF @publisher_login IS NOT NULL
				BEGIN
					SELECT @publisher_security_mode = 0
				END
				-- else use whatever is in the table since it may have been upgraded already
				ELSE
				BEGIN					
					SELECT @publisher_security_mode = publisher_security_mode,
							@publisher_login = publisher_login,
							@publisher_password = publisher_password
					FROM MSsubscription_properties
					WHERE UPPER(publisher) = UPPER(@publisher)
						AND publisher_db = @publisher_db
						AND publication = @publication
				END
			END
			
			-- at this point if security_mode is NULL or 1 set the values
			IF @publisher_security_mode IS NULL
				OR @publisher_security_mode = 1
			BEGIN
				SELECT @publisher_security_mode = 1,
						@publisher_login = N'',
						@publisher_password = @junk_password
			END
			
			/*
			 * Remove/Update command-line parameters
			 */
		
			-- remove the distributor login parameters from the command-line
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorSecurityMode')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorLogin')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorPassword')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'DistributorEncryptedPassword')
			
			-- remove the publisher login parameters from the command-line
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherSecurityMode')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherLogin')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherPassword')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'PublisherEncryptedPassword')

			-- remove the subscriber login parameters from the command-line
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberLogin')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberPassword')
			SELECT @command_line = sys.fn_removeparameterwithargument(@command_line, N'SubscriberEncryptedPassword')
		
			-- ensure that the SubscriberSecurityMode on command-line is 1
			SELECT @command_line = sys.fn_updateparameterwithargument(@command_line, N'SubscriberSecurityMode', N'1')

			/*
			 * Update meta-data table and jobstep command-line
			 */
			
			UPDATE MSsubscription_properties
				SET distributor_security_mode = @distributor_security_mode,
					distributor_login = @distributor_login,
					distributor_password = @distributor_password,
					publisher_security_mode = @publisher_security_mode,
					publisher_login = @publisher_login,
					publisher_password = @publisher_password,
					job_step_uid = @job_step_uid
                WHERE UPPER(publisher) = UPPER(@publisher)
                    AND publisher_db =  @publisher_db
                    AND publication = @publication

			UPDATE msdb.dbo.sysjobsteps
				SET command = @command_line,
					database_name = DB_NAME()
				WHERE job_id = @job_id
					AND (step_uid = @job_step_uid
						OR step_id = @job_step_id)

			FETCH cursorMergeAgents into @publisher, @publisher_db, @publication, @job_id, @job_step_id, @job_step_uid, @command_line
		END
		
		CLOSE cursorMergeAgents
		DEALLOCATE cursorMergeAgents
	END
	
	COMMIT TRANSACTION tr_upgrade_subdb

	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_upgrade_subdb
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSrestoredbreplication (Procedure)
sp_vupgrade_replsecurity_metadata (Procedure)
sp_vupgrade_subscription_databases (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