Usually, one user is created in the sql server, he is given full rights to the database (one - the one with which the system works), and the distribution of rights and the list of users is maintained directly in the system. What kind of authorization will be used for this one user of the sql server is not fundamental, maybe the user is from AD or his sql.
In any case, the system will store its own information for the user (system settings profile, for example), it is quite reasonable to store roles with rights in the same place in the system. Moreover, user rights are not limited to access rights to tables and operations on them. There will be restrictions on the visibility of certain controls and the availability of reports and so on.