Unable to add active directory users to the sharepoint groups

This was something really new we figured out while working on this sharepoint site. We were unable to add the active directory users to the exisiting sharepoint groups in the site. We encountered the following error message “Operation aborted (Exception from HRESULT: 0×80004004 (E_ABORT)”. We had no clue where to look at and how to move ahead! Had some SQL expert advice on this one and eventually figured out the cause. This is how we progressed to resolution…

We edited the web.config file of the affected virtual server in the IIS and set the value for “CallStack” as true and set the value for “Custom Error” as off. We saved the file and reset the IIS. We tried to reproduce the error again and found the following error thread as follows:

Operation aborted (Exception from HRESULT: 0×80004004 (E_ABORT)) at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateMembers(String bstrUrl, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bSendEmail) at Microsoft.SharePoint.Library.SPRequest.UpdateMembers(String bstrUrl, Guid& pguidScopeId, Int32 lGroupID, Int32 lGroupOwnerId, Object& pvarArrayAdd, Object& pvarArrayAddIds, Object& pvarArrayLoginsRemove, Object& pvarArrayIdsRemove, Boolean bSendEmail)

Upon research found that there is option in the SQL Server Instance called XACT_ABORT under Default Connection Options which needs to be unchecked. How would we do that? Follow these simple steps. Open the SQL Server Management Studio and check for the properties of the SQL Server Instance. Select Connection and under Default Connection Options, uncheck XACT_ABORT and save changes. Now that we are done with this we were able to add the users from the active directory to the existing sharepoint groups with no errors.

Here is the cause explained:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. So, if we look at a sharePoint content database, if SQL runs into a transaction that fails, it will roll the changes back, essentially editing the database. If you break the role inheritance on a group you get two membership adherence lines into the database for that group and then when you try to add new users, and you get a SQL exception error, something like ” Violation of PRIMARY KEY constraint “Webmembers_PK”. Cannot insert duplicate key in object ‘dbo.members’. SharePoint functionality is not hindered by this error message but if the “xact abort” SQL option is enabled on the SQL server in the properties of the default instance under connections, then the transaction is rolled back by the SQL server and this generates a HRESULT abort error in SharePoint. This option is turned unchecked by default.