Setting Sql server security rights for multiple situations

Posted by DanDan on Stack Overflow See other posts from Stack Overflow or by DanDan
Published on 2010-05-05T13:10:14Z Indexed on 2010/05/05 13:28 UTC
Read the original article Hit count: 471

Filed under:

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?

Some answers can also be found on serverfault: http://serverfault.com/questions/138763/setting-sql-server-security-rights-for-multiple-situations

Setting Sql server security rights for multiple situations

Posted by DanDan on Server Fault See other posts from Server Fault or by DanDan
Published on 2010-05-05T12:33:32Z Indexed on 2010/05/05 12:38 UTC
Read the original article Hit count: 471

Filed under:

sql-server-2008-r2

|

security

|

logins

|

windows-authentification

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.

Developer IT