I have an C#.NET application and a SQL Server database (SQL Server 2008 R2) and would like to restrict the access to some of the Tables when it has been accessed from some other application rather than my application.
We tried to use a CLR trigger for this. But we couldn't make it. Please give some immediate suggestions. It would be much appreciated!
Best Answer
This is a prime reason why different applications should be using different principals to authenticate on. If you use
Login1for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes.My recommendation? Have a separate login for both of those applications. And then grant the specific permission sets to each of those logins (either directly or through roles).
It is worth noting that a common solution to your problem would be by implementing logic to check for the application name that is passed to SQL Server, but I'm not even going to expand on that solution because it is very easily circumvented. You can basically tell SQL Server that you are
Application1in an ad hoc manner. That approach isn't secure whatsoever. Because of that, I don't recommend you implement this logic.