How can I output a new line with `FORMATMESSAGE` in transact sql?
The problem is the interpretation of \r\n as carrriage return and line feed. SQL Server does interpret \r\n like this in some places, but the FORMATMESSAGE function is not one of them, so instead you need to use CHAR(13) and CHAR(10) which represents the the ASCII codes for Carriage Return (13) and Line Feed (10):

DECLARE @RowCount INT
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + CHAR(13) + CHAR(10) + ' %d users', DB_NAME(), @RowCount+1)


Or use a variable:

DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + @NewLine + ' %d users', DB_NAME(), @RowCount+1)
-- or
DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has %s %d users', DB_NAME(), @NewLine, @RowCount+1)