Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

closed as not constructive by Nick Kavadias, EEAA, rnxrx, Magellan, John Gardeniers Oct 26 '12 at 8:49

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

2 Answers

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.

share|improve this answer
    
Hi Ben and Nick, Thank your for your quick and informative responses. I will look further into application roles to resolve the issue and update this thread with the outcome. –  anand Mar 28 '12 at 4:01
add comment

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

share|improve this answer
add comment

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