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 an application which uses an instance of Sql Server locally for its backend storage. The administrator windows login has had its sysadmin right revoked, and instead two sql logins have been created; one for the application with a secret password and one read only login we let users view the raw data with.

This was working fine until we moved on FileStreams, which requires intergrated windows authentication. So now the sql server logins must be replaced.

As a result, I am now reviewing all of our logins but I am not sure how it is possible. It seems that the application needs full read/write access, yet I still need to lock down writing to the tables so the user cannot login into the database and delete data randomly. Does anyone have any tips for setting multiple levels of security using intergrated windows logins, or can you direct me to any further reading? Thanks.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Take a look at Application Roles which may give you what you need.

Here's a link to get started http://msdn.microsoft.com/en-us/library/ms190998.aspx

share|improve this answer
    
So BUILTIN\Administrators would only have read access into this table, and the Application role would then add on write access? –  DanDan May 5 '10 at 13:57
    
I can't say without knowing more info. When an app logs in with an app role anyone using that app gets the permissions that you previously assigned to the application role. Access is limited connections originating from the app that has logged in. When the user logs in using another context where the app role isn't specified then they'll only have the permissions you've granted them directly or via other role/group membership. –  Chris W May 5 '10 at 14:33
    
If you have to cross databases, guest will have to be enabled on the target. –  jl. May 5 '10 at 16:45

Not sure if this would be the correct way to do it, but could you set up your application to impersonate a windows login account that you have set up just for the application? Then when a user runs the app, it could switch context to that login when it needs to connect to the SQL Server.

share|improve this answer
    
Also, you may want to try asking on StackOverflow as well, this may be considered a programming question, it's kinda in that grey area. –  BlackICE May 5 '10 at 12:50
    
application roles looks like the better answer. –  BlackICE May 5 '10 at 13:03

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.