Configuring Sql Server Security Rights for Multiple Situations
We have an application that uses a Sql Server instance locally for its internal storage. The windows administrator's login had his sysadmin revoked and two sql logins were created instead; one for the app with a secret password and one for read only, we allow users to view the raw data.
This worked fine until we switched to FileStreams, which requires Integrated Windows Authentication. So now we need to replace the sql server logins.
As a result, I go through all our logins, but I'm not sure how this is possible. It looks like the application needs full read / write access, but I still need to lock the write to the tables so that the user cannot log into the database and accidentally delete data. Does anyone have any tips for configuring multiple levels of security using Windows built-in logins, or can you direct me to further reading?
Some answers can also be found on serverfault: https://serverfault.com/questions/138763/setting-sql-server-security-rights-for-multiple-situations
a source to share
You can grant read rights to the Windows logon information that your users will use (that is, an actual user, group, or built-in group such as Users or Everyone). Your app can use a "secret" password agreement and grant the necessary write rights for this approval. Your app logs in as a Windows user, but then it impersonates approval. See Application Roles .
Some notes:
- The administrator can always get access. All you need to do is complete KB 937682: How to add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failover mechanism step by step
- You can never insert "secrets" into an application. You can confuse them at best, but any secret buried in the code or in the config can be found.
a source to share