This code can be simplified a lot, and in to a single update without the cursor. This may be a problem, though if your transaction log is not large enough to accommodate a mass update to all your records.

Right, how to simplify the update?

First, build the case statement in to the @UserCodeList logical table... Consider this:

INSERT INTO @UserCodeList (Title, UserCodeListIdentifier, RepaymentTypeToUpdate)
SELECT Title, UserCodeListIdentifier,
       case
           when title like '%some_text_1%' then 1
           when title like '%some_text_2%' then 2
           .....
       end
FROM tblUserCodeList;

Now, in that table, you have the correct RepaymentType cross reference for the update.

Your update now simply becomes:

UPDATE dbo.tblEmployment
SET RepaymentTypeIdentifier = (
    select UserCodeListIdentifier
    from @UserCodeList
    where RepaymentType = RepaymentTypeToUpdate
)

The above update can be done in a few different ways.... a CTE would be my preference:

with UpdateMap as (
    SELECT Title,
           UserCodeListIdentifier,
           case
               when title like '%some_text_1%' then 1
               when title like '%some_text_2%' then 2
               .....
           end as MatchRepayment
    FROM tblUserCodeList;
)
UPDATE dbo.tblEmployment
SET RepaymentTypeIdentifier = UserCodeListIdentifier
FROM UpdateMap
where MatchRepayment = RepaymentType

This code can be simplified a lot, and in to a single update without the cursor. This may be a problem, though if your transaction log is not large enough to accommodate a mass update to all your records.

Right, how to simplify the update?

First, build the case statement in to the @UserCodeList logical table... Consider this:

INSERT INTO @UserCodeList (Title, UserCodeListIdentifier, RepaymentTypeToUpdate)
SELECT Title, UserCodeListIdentifier,
       case
           when title like '%some_text_1%' then 1
           when title like '%some_text_2%' then 2
           .....
       end
FROM tblUserCodeList;

Now, in that table, you have the correct RepaymentType cross reference for the update.

Your update now simply becomes:

UPDATE dbo.tblEmployment
SET RepaymentTypeIdentifier = (
    select UserCodeListIdentifier
    from @UserCodeList
    where RepaymentType = RepaymentTypeToUpdate
)

The above update can be done in a few different ways.... a CTE would be my preference:

with UpdateMap as (
    SELECT Title,
           UserCodeListIdentifier,
           case
               when title like '%some_text_1%' then 1
               when title like '%some_text_2%' then 2
               .....
           end as MatchRepayment
    FROM tblUserCodeList;
)
UPDATE dbo.tblEmployment
SET RepaymentTypeIdentifier = UserCodeListIdentifier
FROM UpdateMap
where MatchRepayment = RepaymentType

Having said all that, let's revisit your questions:

1. Firstly, is the use of a cursor an overkill here?

   Probably. If your database transaction log is small, though, it may be better to break your updates in to batches that fit. A single large update may fail, and roll back, if there's not enough space to log it as a single operation. Your cursor breaks it doen to a sinlge record each time, and there's plenty of space for that, though. Ask your DBA, or just try it... if you are uncertain.

2. Is there a simpler way to do what I'm doing in SQL?

   SQL is a set-based language. If you have a set-based way of thinking about it, then my suggestions are 'simpler'. Certainly, it is more concise.

3. Could the script go terribly wrong?

   Yes.... it could. Take a backup first, and verify everything!

4. I have limited SQL experience, how does it look? Any standards that I'm not following?

   Your conventions, for what you are doing, are reasonably good. There's very little in the way of 'standards' for SQL style... consistency is the key factor to look for.