[SPSearchServiceApp] Farm account to have db_owner permissions on all Search DBs

[ykuijs]

Details of the scenario you tried and the problem that is occurring

In issue #1289 @rurikon mentioned an issue that occurs when provisioning the Search Service App via PowerShell:

One thing I did notice while doing this is that setting search administrators with SPServiceAppSecurity doesn't work properly either. I have and administrator AD group and while it looks like it's added in the UI, on database level the group doesn't get access to the search databases. When I remove it and add again manually, the database rights are correct. That might explain why I kept having issues where the service pool account's rights would be dropped from one database as described in this blog: https://www.techmikael.com/2014/10/caution-if-you-have-used.html (this happened even though I started creating the service itself with the farm account so it gets the right db owner). This doesn't affect the property issue, but is worth noting if someone else is having the same kind of problems.

Verbose logs showing the problem

Suggested solution to the issue

Add logic to make sure the farm account has db_owner permissions on all Search DBs

The DSC configuration that is used to reproduce the issue (as detailed as possible)

# insert configuration here

The operating system the target node is running

Version and build of PowerShell the target node is running

Version of the DSC module that was used

dev

[ykuijs]

Add logic to check if the farm account is db_owner on all Search DBs and if not, correct this.

[rurikon]

Sorry to return to a closed issue, but I have a few questions. I recently tried to update to version 4.7.0 of SharePointDSC in our test farm and noticed that it now gives an error on the database owner check, failing with "Error while running SQL query: The login already has an account under a different user name."

I went through the changes in the pull request (https://github.com/dsccommunity/SharePointDsc/pull/1319/files) and found that the check is done with this sql query:

SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' AND DP2.name = '$User' AND DP1.name = 'db_owner'

I tried running that on my sql server without the $User restriction and it only returns one line:

DatabaseRoleName   DatabaseUserName
db_owner                  dbo

In fact sys.database_role_members only has one row that connects db_owner database role to the dbo sql user. However, if I look at Security > Logins > select the farm account > Properties > User Mapping, I can see that it has dbo for all search databases in "Users mapped to this login". It also has the db_owner role membership under "Database role membership for: ...", even though I think that's unnecessary when it's already mapped to dbo. This mapping is how SharePoint 2019 (and I think earlier versions too) created those databases when you create a new search service in Central Administration UI.

Is this a difference in versions (I think we are running SQL Server 2019), or single vs. multi server farm, or just a misunderstanding? I don't think there's a way I could install SharePoint so that these could be any different, since I haven't changed them from the defaults.