Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Create a db login and Grant a access permission for only what all table or objects you need to access from your application –  sairam Mar 20 at 10:17

1 Answer 1

This is a prime reason why different applications should be using different principals to authenticate on. If you use Login1 for 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 Application1 in an ad hoc manner. That approach isn't secure whatsoever. Because of that, I don't recommend you implement this logic.

share|improve this answer
1  
This is the only way to securely go. Its what we do where I'm at. The other option is to create a database role and assign the users to that role. That way if you ever want to break out a report user and app user with the same permissions for logging purposes it becomes very easy just to add another user to the DB role. (and with 2012 server roles it becomes even more fun). –  Wind Raven Mar 20 at 12:46
    
Thanks Everyone. I used Application Name in connection string. It is working. –  Jasmine John Apr 1 at 9:58
1  
@JasmineJohn I understand that might be the easiest approach, but I highly recommend against doing that. As per my answer, that implementation is extremely easy to get around with almost no effort. –  Thomas Stringer Apr 1 at 15:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.