locked
Why SQL Sysadmn access on database for installing Forefront protection 2010 for sharepoint RRS feed

  • Question

  • Hi

    Why  required SQL sysadmin role for the account with which we are going to install the FPSP 2010, Is there any particular reason.

    Our SQL admin is not ready to provide the SQL admin role for the account.

    can i do like this ...

    At the time of installation  I will give SQL sysadmin role access for the account with which i install FPSP 2010 and after once it is successfully installed if i remove SQL admin role and provide only DBO access to the database created for SharePoint. what would be the issue we will face in this scenario.

     


    • Edited by RSMR Thursday, October 20, 2011 9:00 AM
    Thursday, October 20, 2011 9:00 AM

Answers

  • Hi,

     

    Thank you for the post.

     

    FPSP uses the Sharepoint object model to navigate/access Sharepoint databases. In order to do this, it needs to be a db_owner of each. As FPSP is installed only once, but Sharepoint databases are typically continuously added to sites, granting the sysadmin rights to the account that accesses databases was chosen as the best method to do this, rather than having to manually add db_owner rights to each database when it is created. I don’t believe that the Sharepoint VSAPI has an option to flag a newly-added database to the AV product, so granting sysadmin rights is the safest way to ensure that FPSP scans any newly-added database from the off.

     

    This question has been raised in the past and there is a minor, yet officially unsupported workaround which some customers have implemented. By “unsupported”, I mean that we will provide “best effort” support, but may decide that a particular issue cannot be resolved and we’d have no obligation to fix it. The workaround involves removing the local administrator account from having sysadmin rights and explicitly granting sysadmin rights in SQL to the Forefront service account instead.

    This is the current status of the issue, as confirmed by our development team.

     

    Regards,


    Nick Gu - MSFT
    Friday, October 21, 2011 9:11 AM

All replies

  • Hi,

     

    Thank you for the post.

     

    FPSP uses the Sharepoint object model to navigate/access Sharepoint databases. In order to do this, it needs to be a db_owner of each. As FPSP is installed only once, but Sharepoint databases are typically continuously added to sites, granting the sysadmin rights to the account that accesses databases was chosen as the best method to do this, rather than having to manually add db_owner rights to each database when it is created. I don’t believe that the Sharepoint VSAPI has an option to flag a newly-added database to the AV product, so granting sysadmin rights is the safest way to ensure that FPSP scans any newly-added database from the off.

     

    This question has been raised in the past and there is a minor, yet officially unsupported workaround which some customers have implemented. By “unsupported”, I mean that we will provide “best effort” support, but may decide that a particular issue cannot be resolved and we’d have no obligation to fix it. The workaround involves removing the local administrator account from having sysadmin rights and explicitly granting sysadmin rights in SQL to the Forefront service account instead.

    This is the current status of the issue, as confirmed by our development team.

     

    Regards,


    Nick Gu - MSFT
    Friday, October 21, 2011 9:11 AM
  • Hi all,

    Let me please participate to a solution suggestion. The model database is created for that issues.

    Suppose that service account login is [PC-Mathias\Mathias].

    1. Install FPSP 2010 with sysadmin role temprary

    2. Remove sysadmin role after install

    3. Create it as db_owner in model database :

    -- ------------------------------------------------------------
    -- Just Trigger the service account dbo role in model database (Do it Once)
    -- ------------------------------------------------------------
    use [model]
    GO
    create user [PC-Mathias\Mathias] FROM LOGIN [PC-Mathias\Mathias]; exec sp_addrolemember 'db_owner',[PC-Mathias\Mathias]
    GO

    4. Suppose that sharepoint create a new database, say [TEST_DB]. The service account will have db_owner role

    -- ------------------------------------------------------------
    -- Test the service account role
    -- ------------------------------------------------------------
    use master
    GO
    create database [TEST_DB]
    GO
    use [TEST_DB]
    GO
    sp_helpuser [PC-Mathias\Mathias]

    5. Result :

    UserName                             RoleName         LoginName                            DefDBName    DefSchemaName UserID     SID
    ------------------------------------ ---------------- ------------------------------------ ------------ ------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    PC-Mathias\Mathias                   db_owner         PC-Mathias\Mathias                   master       dbo           5          0x010500000000000515000000E8D51E18D09F993FEAD906B4E9030000

    Hope that helps.

    P.S. i didn't want a server level trigger which can also do the trick.

    Please let me know if this can be a workaround.

    • Proposed as answer by Mathias FATENE Monday, December 5, 2011 9:58 PM
    Monday, December 5, 2011 9:57 PM