SQL Server EE: Restricting access to users logging in through the Microsoft server management tool [closed]

Question

We have a database in SQL server express edition which is used by a third party application for storing data. When the users login to through this third party program their windows authentication is used and (I assume) their windows authentication is passed on to the sql database as well. We have given users public and dbwriter roles to allow them to be able to write to the database. If we remove them from the dbwriter role the thrid party application cannot exectue the queries it needs.

The issue is by giving the data writer roles to our users we have given them the ability to login through the server management tool and manually change the data. We cannot allow this due to our audit guidelines.

Ideally, I would like to make it so when a user is in a certain role they cant login through the server management utility to make changes however when they login through the thrid party software and make the changes required by the thrid party software. The only solution I have come with so far is to change the login type to the database login assigned during the creation of the database when someone tries to login through the server utility and use windows authentication with the third party application. This is not a great solution and hence why I am here. Any ideas will be greatly appericiated!

Sincerely, Anand

Answer 1

While it could be that the application is passing on the end user's Windows credentials, it might not as well. Fire up SQL profiler and see what login name is executing data changes. A lot of applications have the application server do data manipulation on behalf of the user and so you might see that account doing the action. If not, using an application role might be in order. Check out http://msdn.microsoft.com/en-us/library/ms190998.aspx.

Answer 2

Using Applications Roles is the best way to do this. Assuming you're using C# here's some sample code.