Help setting appropriate permissions RRS feed

  • Question

  • 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, datareader

    Permissions we want for the test databases:

    -          UserSite: none

    -          TestSite: datareader, datawriter, db_owner

    -          MaintenanceSite: datareader, datawriter, db_owner

    Are 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

    Friday, August 10, 2012 6:28 PM


  • Yes, when you restore a database, database permissions are retained. Server-level permissions are of course not retained, but it did not seem that you had any of them in the list, save for sysadmin for MaintenanceSite.

    I can't say what is appropriate permissions for you, since I don't know your application, business requirements etc. But I don't like seeing db_owner on the list, and I think you should make a review of whether this is needed. It is always good to run applications with restricted permissions. If there is a vulnerability due to SQL injection or something else, the intruder is limited in what he can do.

    MaintenanceSite should not need sysadmin to do restores. As long as the database exists, db_owner is sufficient. If the database does not exist, the app pool needs to have CREATE DATABASE permission or be member of the dbcreator server role.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by amber zhang Friday, August 17, 2012 8:26 AM
    Friday, August 10, 2012 9:45 PM