Escaping a single quote in SQL Server
The user name is an ID/QUOTED ID that should be enclosed in double quotes or square brackets while the password is a regular string where single quotes need to be doubled. The correct syntax would in other words be;

CREATE LOGIN "Ng'ang'a" WITH PASSWORD='Ng''0ng''0'


or

CREATE LOGIN [Ng'ang'a] WITH PASSWORD='Ng''0ng''0'