We Have A List Of 3 Sql Server Logins Which Are Dedicated To A Critic

1.	We Have A List Of 3 Sql Server Logins Which Are Dedicated To A Critical Application. We Have Given All Required Rights To Those Logins. Now My Question Is We Have To Restrict The Access Only To These Three Logins. Means There Are Two Conditions: A) No Other User Should Be Able To Access The Database Except Those Three Logins B) Even For Those Three Logins They Should Be Able To Run Their Queries Only Through The Application. If Someone Login Through Ssms And Trying To Run A Query Should Result Into A Failure. Finally There Should Be Only Way To Running A Query Is From Their Application Using One Of Those Three Logins, There Should Be No Other Way To Run Queries On That Database. How Do You Restrict?
Answer» Do not GIVE access to any other login on that database except for those 3 app LOGINS. Create a trigger that test each and every query like below IF app_name() in(‘SQL Query ANALYZER’,’Microsoft SQL Server Management Studio’) raiserror (…..) Return Do not give access to any other login on that database except for those 3 app logins. Create a trigger that test each and every query like below IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’) raiserror (…..) Return

We Have A List Of 3 Sql Server Logins Which Are Dedicated To A Critical Application. We Have Given All Required Rights To Those Logins. Now My Question Is We Have To Restrict The Access Only To These Three Logins. Means There Are Two Conditions: A) No Other User Should Be Able To Access The Database Except Those Three Logins B) Even For Those Three Logins They Should Be Able To Run Their Queries Only Through The Application. If Someone Login Through Ssms And Trying To Run A Query Should Result Into A Failure. Finally There Should Be Only Way To Running A Query Is From Their Application Using One Of Those Three Logins, There Should Be No Other Way To Run Queries On That Database. How Do You Restrict?

Answer»

Do not GIVE access to any other login on that database except for those 3 app LOGINS.

Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query ANALYZER’,’Microsoft SQL Server Management Studio’)

raiserror (…..)

Return

Do not give access to any other login on that database except for those 3 app logins.

Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)