none
PSDataAccess - Can someone check if their farm is configured with this role ? RRS feed

  • Question

  • We have SP 2016 Farm ( with builtin project server ) - the content databases have a role PSDataAcceess which is set to DENY access to several database objects ( Database  > Security > Roles > Database Roles  > PSDataAccess  > Properties > Securables tab, select one of the objects below ).

    Can someone check if their farm has such DB role set to DENY access to several farm accounts ?  This configuration is blocking us applying CUs...


    • Edited by Name6666666 Monday, January 20, 2020 11:15 AM
    Monday, January 20, 2020 11:12 AM

All replies

  • Hi, Name6666666,

    The SPDataAccess role is the default role for database access and should be used for all object model level access to databases. Add the application pool account to this role during upgrade or new deployments.

    • The SPDataAccess role will have the following permissions:

      Grant EXECUTE or SELECT on all SharePoint stored procedures and functions.

      Grant SELECT on all SharePoint tables.

      Grant EXECUTE on User-defined type where schema is dbo.

      Grant INSERT on AllUserDataJunctions table.

      Grant UPDATE on Sites view.

      Grant UPDATE on UserData view.

      Grant UPDATE on AllUserData table.

      Grant INSERT and DELETE on NameValuePair tables.

      Grant create table permission.

    Reference:https://docs.microsoft.com/en-us/sharepoint/install/account-permissions-and-security-settings-in-sharepoint-server-2016

    The configuration in my end of a farm account is like this:

    Best Regards

    Jerry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, January 21, 2020 2:12 AM
  • DENY would be correct for certain objects in your database, e.g. Project Server tables (might not be all tables, but certainly is some).

    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Tuesday, January 21, 2020 2:31 AM
    Moderator
  • Hi, Name6666666,

    The SPDataAccess role is the default role for database access and should be used for all object model level access to databases. Add the application pool account to this role during upgrade or new deployments.

    • ...

    Jerry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Dear - don't be confused I am not talking about SPDataAccess role . But different one - PSDataAccess  -  I could not find much info on PSDataAccess so far. Do you even have PSDataAccss in SQL Studio?

    Tuesday, January 21, 2020 9:50 AM
  • DENY would be correct for certain objects in your database, e.g. Project Server tables (might not be all tables, but certainly is some).

    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    The PSDataAccess role ( not SPDataAccess ) seems to deny selects/inserts  to 

    - the IIS Web App Pool account  which runs the corresponding content database,

    - also denied is access to a  Service Applications account

    - and to a Project Server account .....

    Yes the DENY to above accounts seem to apply only to ProjectServer tables .... ( Seen in PSDataRole properties / Securabes )

    Reason I found out about this is because some content databases in addition to above mentioned accounts also had Farm account and Farm administrator accounts as members of this PSDataRole which was blocking patching .. this farm had just been upgraded from SharePoint 2013 with database detach / attach.





    Tuesday, January 21, 2020 9:59 AM
  • This can happen if you created your CDBs with a user other than the farm admin account (e.g. if using PowerShell, best practice is your personal elevated account) or changed farm admin accounts during your upgrade.

    To fix it, make sure your farm admin account is mapped to the dbo role.


    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Tuesday, January 21, 2020 3:34 PM
    Moderator
  • This is also happening to us.  

    Installed binaries on all servers with success: 
    sts2016-kb4484215-fullfile-x64-glb.exe
    wssloc2016-kb4484220-fullfile-x64-glb.exe


    The patch failed during the Upgrade phase when I installed it on my Dev farm (SP 2016 Enterprise + Project Server, 16.0.4948.1000. Seems to be failing on a lack of permission in content databases.

    Basically the Upgrade wizard keeps failing due to a permissions issue (related to Project Server) on content databases. I found out that the Farm account is part of a database role (PSDataAccess) that actually has a DENY permission set for Insert and Select statements.

    One possible workaround is to remove the farm account from the PSDataAccess role for each database. I tested this on one database and it upgraded with success (using Upgrade-SPContentDatabase).

    I don’t know why it’s doing this NOW. I haven’t changed permissions on these databases since the initial farm setup. We've patched this farm numerous times, but this is the first time a patch has complained about those permissions, let alone failing the upgrade.

    I am also unsure of whether it’s appropriate to remove the Farm account from that DB role. I don’t remember setting that permission before so it’s possible the role was automatically assigned as a farm operation; e.g. when I installed Project Server.

    It is failing on every content database.  Service databases seem unaffected.  If I detach a content DB and rerun PSConfig (or PSConfigUI) it simply fails on the next content DB. In both cases I am running in the context of the Farm account.Running Test-SPContentDatabase shows no issues. 

    The -Force parameter does not work around the issue.  

    Per ULSViewer, it appears the step was attempting this: 

    PDEUpgraderAction 'One time population of ProjectLastPublishedDate column in RDBSCHEMA.MSP_EpmProject from PUBSCHEMA.MSP_PROJECTS' is being executed because ShouldSkipUpgraders is set to False

    Running PSConfigUI, I get this error:

    An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information:
    Action 16.1.313.0 of Microsoft.Office.Project.Server.Database.Extension.Upgrade.PDEUpgradeSequence failed. (EventID:ajywk)
    Exception: The SELECT permission was denied on the object ‘MSP_PROJECTS’, database ‘My_Content_Database’, schema ‘pjpub’. (EventID:ajywk)
    (EventID:ajywk)
    Upgrade [SPContentDatabase Name=My_Content_Database] failed. (EventID:an59t)
    Inner Exception: The SELECT permission was denied on the object ‘MSP_PROJECTS’, database ‘My_Content_Database’, schema ‘pjpub’. (EventID:an59t)
    (EventID:an59t)
    Exception: Action 16.1.313.0 of Microsoft.Office.Project.Server.Database.Extension.Upgrade.PDEUpgradeSequence failed. (EventID:an59t)
    (EventID:an59t)
    Upgrade Timer job is exiting due to exception: Microsoft.SharePoint.Upgrade.SPUpgradeException: Action 16.1.313.0 of Microsoft.Office.Project.Server.Database.Extension.Upgrade.PDEUpgradeSequence failed. —> System.Data.SqlClient.SqlException: The SELECT permission was denied on the object ‘MSP_PROJECTS’, database ‘My_Content_Database’, schema ‘pjpub’. — End of inner exception stack trace —

    As I mentioned above, I found a workaround so I could upgrade a database individually (using Upgrade-SPContentDatabase). But I went back to check the permissions for that SQL login and found that the PSDataAccess role had been re-added for the Farm account for that database. Apparently there’s a timer job that manages that security and potentially overrides your changes. That would make my workaround kind of useless, then.

    @Trevor I will check the DBO role membership for the farm account for each DB.  However I can confirm this is happening for every content db (so far) both new databases and those we migrated from 2013. 

    Edit: The farm account has the DB_Owner role for each content DB, and as far as I can tell, this also correlates to the DBO role.  In the currently failing content database, for example, the Farm account is a member of the DBO role.  

    The PSDataAccess role for that database includes the Farm account as well as a few other service accounts I created for the farm: a generic services account, and an app pool account.  
    • Edited by poortatey Tuesday, January 21, 2020 5:24 PM Adding findings
    Tuesday, January 21, 2020 5:09 PM
  • Don't remove the farm admin account from the role. Can you take a screenshot of the Database Owner and dbo role mapping?

    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Tuesday, January 21, 2020 5:28 PM
    Moderator
  • Here is the properties of the Farm account --> User Mapping, showing DB roles it has for a given database: 

    Here is the properties for the DB_Owner role in the same database: 

    I may be mistaken when it comes to "DB_Owner" being the same as the "DBO" role, I guess.  I'm a little rusty on my SQL.  

    Tuesday, January 21, 2020 6:03 PM
  • I guess my question is, both me and the OP are experiencing the same issue.  Is this a bug in the Jan 2020 patch, which shows up if you have Project Server installed? 
    Tuesday, January 21, 2020 7:13 PM
  • I'm not sure about a "bug", but it certainly is an issue -- in fact, I had an open support case for this and it was due to dbo mapping. Dbo needs to be mapped to the account running owstimer.exe.

    This issue was introduced in the Dec 2019 PU.


    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Tuesday, January 21, 2020 8:47 PM
    Moderator
  • I have my 2016 farm with Project Server installed and did not experience any errors with January 2020 CU.
    Wednesday, January 22, 2020 1:50 PM
  • I have my 2016 farm with Project Server installed and did not experience any errors with January 2020 CU.

    It depends on how the content databases were provisioned. If they were provisioned via Central Admin, you won't experience this issue as the farm service account is mapped to dbo. If they were provisioned by an elevated access account (not the farm service account) via PowerShell, you should experience this issue.

    In the OP's case, the mapped account may not be the same as the previous farm.


    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, January 22, 2020 3:39 PM
    Moderator
  • Just wanted to confirm, we tested this on one of the content databases.  The current owner was "SA", presumably set by the DBA when he restored the SP 2013 database to that SQL instance.  We changed to a new owner by using the sproc: Exec sp_changedbowner 'Contoso\SP16TST_Farm', which remapped the dbo database user to the Farm login. 

    Then I ran Upgrade-SPContentDatabase -WebApplication https://portal.contoso.com -Name MyFarm_Portal_Content 

    This completed with success!  Cheers, Trevor!

    I'm waiting on my DBAs to change the rest of them over. 

    ---

    Note that if you run into an error stating that the farm db user (Contoso\SP16TST_Farm) already exists in the database, you will need to drop the existing DB user and then rerun the sp_changedbowner sproc, and the command will succeed.

    My DBA said that, before dropping the Contoso\SP16TST_Farm db user, he transferred all of schemas owned by the Contoso\SP16TST_Farm db user to the DBO db user.  Not sure if that step was needed, but better safe than sorry. 

    Ref: https://www.sqlservercentral.com/forums/topic/the-proposed-new-database-owner-is-already-a-user-or-aliased-in-the-database


    • Edited by poortatey Wednesday, January 29, 2020 6:45 PM Clarity
    Wednesday, January 29, 2020 6:40 PM