SQL Server Authentication or Integrated Security?

Question

We have corporate intranet users using the WinForms application to work with the system with the SQL server behind. An integrated security system is configured, allowing all users to update and remove permissions when application security restrictions are limited by how and where table updates occur.

However, some users are powerful users with SQL query tools at their disposal and directly access the database for reporting. However, with integrated security, they have default update rights for tables where they should not be, because the application applies the rules to updates.

Is this an example of where it is more convenient if the application has a central authenticated login to the SQL system, while users are granted read-only rights for integrated security?

Answer 1

As John said, stored procedures will provide you protection against direct table changes. There are other options. You can use the "application role" of SQL Server (via the sp_setapprole procedure). This allows you to continue to use a separate identifier for everyone, but only when connecting applications (via the interface) user rights are increased.

, , SQL , , , .

- . , . - SQL. , , , , .. .

Answer 2

, , SQL. , , exec . , , , .

Answer 3

SQL- - . - . , - , .

, . , , SQL, , . .

, , . .

Answer 4

. Integrated security, SP .

Access to advanced access can be provided to database administrators to directly manage data if necessary.

Group-based permissions give you much more flexibility for access rights and less administrative burden when controlling them with integrated security.