SQL Server Authorization/Security Question

Question

We have built a C# framework that all data modifications to our database should go through. We do not want anyone to make data changes directly to the database. All changes must go through our framework.

My question is how to enforce this? How can we have SQL Server only allow Insert/Update/Delete statements that are being made from our framework, and not any one of potentially hundreds of individuals who might be connected via SSMS or some other tool?

I don't have a great deal of experience with SQL Server security/authentication so I need some direction on this.

Thanks very much.

Answer 1

One way would be through use of triggers and checking either app_name, host_name

if app_name() in('SQL Query Analyzer','Microsoft SQL Server Management Studio')
raiserror (.....)
return

or deny write/update/delete access to all usernames but the one that your app uses

Answer 2

Create a SQL login for your application and grant it permissions to stored procedures or base tables - whatever your needs are.

Don't grant access to anyone else.

But you mention a 'framework' which means many applications and usernames will be using it...so maybe you need another approach.

Answer 3

Your framework should access SQL under its own credentials whether it be a sql login or a domain account that the framework runs under. Grant the appropriate SQL permissions to that account.