What is the best authorization approach?

  1. Built-in Windows
  2. SQL Server authorization
  3. Combining SQL Server authorization and one’s own — there are tables of users, roles, etc. for example, one login is created for each role.

I would very much like to hear the opinion of those who faced a similar task. Thank you in advance

    1 answer 1

    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.

    • And what if you need to track changes (who and when)? In the case of Windows or MS SQL authorization of any problems - we get the username and all. Whereas in the case of a proprietary system, it will be necessary for each table to create a field that was last edited, so that it can be passed to the INSERT \ UPDATE constructs for further processing, for example, the trigger Donil
    • The change control system can be quite complex. Who and when is the simplest case, which may not be enough, and you will have to create change logs, etc. And then it is better to work with your data, and not with mssql users. Yes, if there is a need for change control, triggers are the right decision. In addition, there is another reason to use its users - this is the institution of new and editing existing ones (editing roles, etc.). It is easier to do this in the program, than to call a DBA that will add the user to mssql or correct something. - Yura Ivanov
    • In addition, imagine that you need to transfer the database to another server, you will have to suffer again with the transfer of users and roles - that is still a joy ... - Yura Ivanov