Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepladdproxyaccount

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepladdproxyaccount
(
	@job_name			sysname,
	@owner_login_name	sysname,
	@subsystem			sysname,
	@job_login			nvarchar(257) = NULL,
	@job_password		sysname = NULL,
	@proxy_id			int = NULL OUTPUT
)
AS
BEGIN
	DECLARE @retval					int,
			@create_credential_ddl	nvarchar(4000),
			@proxy_credential_name	sysname,
			@maxpartlen				int,
			@count					int,
			@count_str				sysname

	-- Verify that the subsystem to be modified is a repl job
	IF @subsystem NOT IN (N'Distribution',
							N'Merge',
							N'LogReader',
							N'Snapshot',
							N'QueueReader')
	BEGIN
		-- Only replication jobs or job schedules can be added, modified or dropped via replication stored procedures.
		RAISERROR(22538, 16, -1)
		RETURN 1
	END

	-- ensure that job login was provided
    IF @job_login IS NULL
    BEGIN
        -- MSG : not localized because it is only for DEVs using this proc
        RAISERROR('@job_login must be a non-null/non-empty value.', 16, -1)
        RETURN 1
    END

	BEGIN TRANSACTION tr_MSrepladdproxyaccount
	SAVE TRANSACTION tr_MSrepladdproxyaccount

	-- get an applock to avoid failures from another
	-- repl user creating the same credential name.
	-- exit if it takes longer than 30 seconds....
	EXEC @retval = sys.sp_getapplock @Resource = 'Create_Repl_Credential',
                                    @LockMode = 'Exclusive',
                                    @LockOwner = 'Transaction',
                                    @LockTimeout = -1, -- infinite
                                    @DbPrincipal = N'db_owner'
    IF @@ERROR <> 0 or @retval < 0
		GOTO FAILURE

    -- set the password to empty string if it
    -- is NULL and the job_login was specified
    -- ALTER will complain with NULL password
    SELECT @job_password = ISNULL(@job_password, N'')

	-- @proxy_credential_name will be [REPL][JOBLOGIN][JOBNAME].ID
	--  	extra char count 	= [REPL][][] 							= 10
	--		JOBLOGIN max len 	= (len(sysname) - (extra char count))/2 = (128 - 10)/2
	--		JOBNAME max len 	= (len(sysname) - (extra char count))/2 = (128 - 10)/2
	SELECT @count = 0,
			@maxpartlen = 59,
			@proxy_credential_name = '[REPL][' + substring(@job_login, 1, @maxpartlen) + ']'
										+ '[' + substring(@job_name, 1, @maxpartlen) + ']'
	
    WHILE EXISTS(SELECT name
        			FROM sys.credentials
        			WHERE name = @proxy_credential_name)
        	
		OR EXISTS(SELECT name
        			FROM msdb.dbo.sysproxies
        			WHERE name = @proxy_credential_name)
    BEGIN
        -- here we tack on an incremented id because we have a name collision
        
   		-- @proxy_credential_name will now be [REPL][JOBLOGIN][JOBNAME].ID
		--  	extra char count 	= [REPL][][]. + len(ID) 				= 11 + len(ID)
		--		JOBLOGIN max len 	= (len(sysname) - (extra char count))/2 = (128 - 11 - len(ID))/2
		--		JOBNAME max len 	= (len(sysname) - (extra char count))/2 = (128 - 11 - len(ID))/2
        SELECT @count = @count + 1,
				@count_str = cast(@count as nvarchar),
				@maxpartlen = (117 - len(@count_str))/2,
				@proxy_credential_name = '[REPL][' + substring(@job_login, 1, @maxpartlen) + ']'
											+ '[' + substring(@job_name, 1, @maxpartlen) + '].'
											+ @count_str
    END

    SELECT @create_credential_ddl = 'CREATE CREDENTIAL ' + QUOTENAME(@proxy_credential_name) +
			        				' WITH IDENTITY = N''' + replace(@job_login, '''', '''''') +
									''' , SECRET = ' + QUOTENAME(@job_password, '''')
	EXEC(@create_credential_ddl)
	IF @@ERROR <> 0
		GOTO FAILURE

	-- create the proxy account and map to the specific replication job subsystem
	EXEC @retval = msdb.dbo.sp_add_proxy @proxy_name = @proxy_credential_name,
								@credential_name = @proxy_credential_name,
								@proxy_id = @proxy_id OUTPUT
	IF @@ERROR <> 0 or @retval <> 0
		GOTO FAILURE

	EXEC @retval = msdb.dbo.sp_grant_proxy_to_subsystem @subsystem_name = @subsystem,
												@proxy_name = @proxy_credential_name
	IF @@ERROR <> 0 or @retval <> 0
		GOTO FAILURE

	IF ISNULL(IS_SRVROLEMEMBER('sysadmin', @owner_login_name), 0) = 0
	BEGIN
		EXEC @retval = msdb.dbo.sp_grant_login_to_proxy @proxy_name = @proxy_credential_name,
												@login_name = @owner_login_name
		IF @@ERROR <> 0 or @retval <> 0
			GOTO FAILURE
	END
	
	-- we release the applock at this point because
	-- the remaining steps will not be affected by
	-- any type of name collisions etc...
    EXEC @retval = sys.sp_releaseapplock @Resource = 'Create_Repl_Credential',
					                            @LockOwner =  'Transaction',
					                            @DbPrincipal =  'db_owner'
    IF @@ERROR <> 0 or @retval <> 0
		GOTO FAILURE

	COMMIT TRANSACTION tr_MSrepladdproxyaccount
	
	RETURN 0
FAILURE:
	ROLLBACK TRANSACTION tr_MSrepladdproxyaccount
	COMMIT TRANSACTION
	
	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_MSadd_repl_job_unsafe (Procedure)
sp_MSchange_repl_job_unsafe (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