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