COURSE of the MONTH

How to Allow users who are using a specific Application to modify database

hello
we have sql server database "MyCompanyDB" "sql server 2012,Windows 2008R2"
there is a desktop Application and a website , both connected to this database "MyCompanyDB"
what i need is to prevent any Data modification "Update/Delete/add" UNLESS the end user use these Applications ... for example if a developer or user use a third party application that connect the database he cant modify the data even from management studio
how can i implement that
Note: we are using SQL Server Authentication as the server authentication

Who is Participating?

Have the application set a specific APP_NAME when it makes its connection to SQL Server.

Then add DML triggers on the table that reject any mods unless the APP_NAME matches what you specified in the valid application.

For example:

CREATE TRIGGER table1_trigger_verify_app
ON dbo.table1
AFTER DELETE, INSERT, UPDATE
AS
SET NOCOUNT ON
--if app name shows it's from good app, exit from trigger, which processes mods normally
IF APP_NAME() = 'Your_good_application_name_Bb78-E5D2a10597BB'
RETURN
--missing or invalid app name, so rollback any/all mods user tried to make
RAISERROR('You cannot make any modifications to these tables outside the main application.', 16, 1)
ROLLBACK TRANSACTION
GO

this will deny access to any database unless the user is sysadmin or DB owner, you can then create read only logins dbowner for those you want to read db and read/write dbowner for application and website