What is the correct way to send several datasets in the response using Nodejs and mssql?

Although the question is something old I think the motivation is interesting, so I'm going to leave this answer in case someone comes to this page looking for an answer.

How and how you have defined Role and User what you are saying is correct. Each operation must be done in a new nested callback since they are asynchronous operations.

One way to avoid so much nesting in the code is by extracting the code from the functions. For example:

function getUsers (req, res) {
    const sendResult = function (users, roles) {
        res.status(200).render('users', { users, roles });
    }

    const findUserCallback = function (recordSet) {
        const users = recordSet[0];

        const findRolCallback = function (recordSet) {
            const roles = recordSet[0];
            sendResult(users, roles);
        }

        Rol.findRol(null, null, findRolCallback);
    }

    User.findUser(null, null, null, findUserCallback);
}

Notice that although we have changed the structure of the code, the operation has not changed. Each function still has the call to the next one nested in its callback. We just flattened the code. Even so, if we add more calls to BD at the end we would end up in the same situation.

Having said that, I see that you are using promises in both db.js and in the implementation of Role and User. Is there any reason not to extend its use to the getUsers function?

For example, if we modify findRol in the following way:

// callback es opcional
// si es proporcionado será usado,
// en caso contrario se devolverá una promesa
function findRol (rolId = null, active = null, callback) {
    const promise = new Promise((resolve, reject) => {
        db.sendRequest((cp) => {
            new sql.Request(cp)
            .input('rolid', rolId)
            .input('active', active)
            .execute('crmFindRol')
            .then(recordSet => resolve(recordSet))
            .catch(err => reject(err));
        });
    });

    // si no se ha proporcionado callback devolvemos la promesa
    if (!callback) {
        return promise;
    }

    // capturamos el resultado de la promesa y llamámos al callback 
    promise
    .then(result => callback(result))
    .catch(err => console.log(err));
}

The user of the function can decide if he wants to use promises or callbacks.

If we used promises, in the function getUsers we could do the following (assuming that we have modified findUser in a similar way):

function getUsers (req, res) {
   // Como no pasamos callback ambas funciones
   // devuelven una promesa.
   // Las guardamos en una array.
   const loadDataPromises = [
       User.findUser(null, null, null),
       Rol.findRol(null, null)
   ];

   // Esperamos a que todas las promesas hayan sido resueltas
   Promise.all(loadDataPromises)
   .then((results) => {
       // results contiene los resultados de las promesas
       // de la array loadDataPromises en el mismo orden
       const users = results[0][0];
       const roles = results[1][0];

       res.status(200).render('users', { users, roles });
   })
   // El problema de este método es que si falla una
   // sola promesa saltamos al catch directamente.
   // En este caso diría que es un comportamiento
   // adecuado, pero no siempre lo será.
   .catch(err => console.log(err));
}

As you can see instead of having a callback for each function we are simply adding promises to the array loadDataPromises , we wait for all of them to finish and we get the results with Promise.all (loadDataPromises) This way the code will never be nested more than it already is, even if we make hundreds of requests to BD.

Promises give a lot of play, but like callbacks have their own dangers. If you are going to seriously develop code in Node.js I advise you to take a look at its operation.