Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debwuthnow
Starting Member
1 Post |
 Posted - 2012-08-10 : 14:40:03
|
| We have three applications each on a different website that need to interact with a set of 3 databases with varied permission and need some help to make sure we set those permissions correctly. We’ve identified three levels of permission/accessibility and have separated websites into three separate Integrated App Pools. Each Integrated App Pool has its own automatically-created Windows account and this allows us to use Windows Authentication to access SQL Server for greater security and convenience. The Windows accounts are as follows:UserSite – read access to 2 live databases and write to the log.TestSite – a version of those live data applications, but on a test website for staging upgrades; needs to have read/write permission.MaintenanceSite – used internally to perform backup/restoration to the various test sites. Needs to be able to backup all the sites and restore to the test sites. We’re assuming that when we restore the live data into a test system that it keeps the permissions from its source and not the destination permissions. Is that correct? If so, what is the best way to automate changing the permissions after a restore?Permissions we want for the live databases: (are these the correct fixed database roles to accomplish our goals?)- UserSite: datareader, datawriter, db_owner- TestSite: none- MaintenanceSite: bkupOperator, datareaderPermissions we want for the test databases:- UserSite: none- TestSite: datareader, datawriter, db_owner- MaintenanceSite: datareader, datawriter, db_ownerAre there server roles we need for the app pools? I’ve given the sysadmin server role to the MaintenanceSite so that it can perform restores. My goal is to assign just enough permission to accomplish our goals yet still protect the data. Suggestions? Using SQL Server 2012 and IIS 7.5 |
|
|
|
|
|
Security Permission Help