IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_DB_GrantUserRoleMember') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CP_DB_GrantUserRoleMember 
END
GO

CREATE PROC dbo.CP_DB_GrantUserRoleMember
  @NewLogin sysname,
  @LoginToClone sysname,
  @DBName sysname
AS
BEGIN
SET NOCOUNT ON;

DECLARE @TSQL nvarchar(MAX);
DECLARE @Return int;
DECLARE @Ver DECIMAL(5,2);

SET @Ver = dbo.CF_DB_Version();

IF @Ver >= 12
	BEGIN TRAN; 

CREATE TABLE #RoleMembershipSQL  (RoleMembersTSQL nvarchar(MAX));

SET @TSQL = 'INSERT INTO #RoleMembershipSQL (RoleMembersTSQL) 
				SELECT ''EXEC sp_addrolemember @rolename = '''''' + r.name 
					+ '''''', @membername = ''''' + @NewLogin + ''''';''
				FROM [' + @DBName + '].sys.database_principals AS U
					JOIN [' + @DBName + '].sys.database_role_members AS RM
					ON U.principal_id = RM.member_principal_id
					JOIN [' + @DBName + '].sys.database_principals AS R
					ON RM.role_principal_id = R.principal_id
				WHERE U.name = ''' + @LoginToClone + ''';';

EXEC @Return = sp_executesql @TSQL;

IF (@Return <> 0)
BEGIN
	IF @Ver >= 12
		ROLLBACK TRAN; 
	RAISERROR('Could not retrieve role memberships.', 16, 1);
	RETURN(1)
END;

DECLARE cursDBRoleMembersSQL CURSOR FAST_FORWARD FOR SELECT RoleMembersTSQL FROM #RoleMembershipSQL;
OPEN cursDBRoleMembersSQL;
FETCH FROM cursDBRoleMembersSQL INTO @TSQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SET @TSQL = 'USE [' + @DBName + ']; ' + @TSQL;
    EXECUTE @Return = sp_executesql @TSQL;

    IF (@RETURN <> 0)
    BEGIN
        IF @Ver >= 12
			ROLLBACK TRAN;
	    RAISERROR('Error encountered assigning DB role memberships.', 16, 1);
		CLOSE cursDBRoleMembersSQL;
		DEALLOCATE cursDBRoleMembersSQL;
    END;

    FETCH NEXT FROM cursDBRoleMembersSQL INTO @TSQL;
END;

CLOSE cursDBRoleMembersSQL;
DEALLOCATE cursDBRoleMembersSQL;

DROP TABLE #RoleMembershipSQL;

IF @Ver >= 12
	COMMIT TRAN;
END;