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