Skip to content

Sharepoint 2013 Orphan Type Roleassignments

Hi,

im getting the same warning in my health analyzer. System Configuration is nearly the same. It is also a fresh install for a test enviroment with Arpil CU 2014. Only difference is SP Standard Edition. But also a Single Server with SQL Server 2012.

Error is on all Contentdatabases (also Admin_Content).

ULS Errorlog shows the following Exception:

A health rule of type Microsoft.SharePoint.Administration.Health.ContentOrphans, Microsoft.SharePoint.Health, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c failed to execute. System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'proc_SecRepairOrphanRoleAssignments' expects parameter '@Fix', which was not supplied.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)
    at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior)
    at Microsoft.SharePoint.Administration.SPContentDatabase.RepairOrphanRoleAssignments(Boolean fix)
    at Microsoft.SharePoint.Administration.SPContentDatabase.RepairCore(Boolean DeleteCorruption, Boolean collectResults, UInt32& orphanCount)
    at Microsoft.SharePoint.Administration.Health.ContentOrphans.Check()
    at Microsoft.SharePoint.Administration.Health.SPHealthAnalyzer.ExecuteRuleCore(SPHealthAnalysisRule rule, Boolean repairAutomatically, SPHealthCheckStatus& status, SPHealthCheckErrorLevel& effectiveSeverity)  ClientConnectionId:2d774fbb-56b4-49de-897f-23c439e41331. System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'proc_SecRepairOrphanRoleAssignments' expects parameter '@Fix', which was not supplied.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)
    at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior)
    at Microsoft.SharePoint.Administration.SPContentDatabase.RepairOrphanRoleAssignments(Boolean fix)
    at Microsoft.SharePoint.Administration.SPContentDatabase.RepairCore(Boolean DeleteCorruption, Boolean collectResults, UInt32& orphanCount)
    at Microsoft.SharePoint.Administration.Health.ContentOrphans.Check()
    at Microsoft.SharePoint.Administration.Health.SPHealthAnalyzer.ExecuteRuleCore(SPHealthAnalysisRule rule, Boolean repairAutomatically, SPHealthCheckStatus& status, SPHealthCheckErrorLevel& effectiveSeverity)  ClientConnectionId:2d774fbb-56b4-49de-897f-23c439e41331 Source: .Net SqlClient Data Provider Server: FUT-SP03\SharePoint Procedure: proc_SecRepairOrphanRoleAssignments StackTrace:  at onetnative.dll: (sig=8299e000-0f80-4b71-8567-479075c2110d|2|onetnative.pdb, offset=28B42) at onetnative.dll: (offset=152A9)

The solution of Eric Tao is not working. SharePoint cannot repair it automatically.

Also a recreation of Webapp and Contentdatabases didn´t solve the issue.

Installation of SP was with SP2013 Slipstream SP1 ISO, Laguagepack DE Slipstream SP1 Iso and April CU 2014.

Regards,

Andre



-- Scripting Out the Logins, Server Role Assignments, and Server Permissions

-- ************************************************************************************************************************

-- Copyright © 2015 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

 

-- CRITICAL NOTE: You’ll need to change your results to display more characters in the query result.

-- Under Tools –> Options –> Query Results –> SQL Server –> Results to Text to increase the maximum number of characters

-- returned to 8192 the maximum or to a number high enough to prevent the results being truncated.

-- ************************************************************************************************************************

SETNOCOUNTON

-- Scripting Out the Logins To Be Created

SELECT'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN '+QUOTENAME(SP.name)+

   CASE

WHENSP.type_desc='SQL_LOGIN'THEN' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED, CHECK_EXPIRATION = '

+CASEWHENSL.is_expiration_checked=1THEN'ON'ELSE'OFF'END+', CHECK_POLICY = '+CASEWHENSL.is_policy_checked=1THEN'ON,'ELSE'OFF,'END

ELSE' FROM WINDOWS WITH'

END

   +' DEFAULT_DATABASE=['+SP.default_database_name+'], DEFAULT_LANGUAGE=['+SP.default_language_name+'] END;'COLLATESQL_Latin1_General_CP1_CI_ASAS[-- Logins To Be Created --]

FROMsys.server_principalsASSPLEFTJOINsys.sql_loginsASSL

ONSP.principal_id=SL.principal_id

WHERESP.typeIN('S','G','U')

ANDSP.nameNOTLIKE'##%##'

ANDSP.nameNOTLIKE'NT AUTHORITY%'

ANDSP.nameNOTLIKE'NT SERVICE%'

ANDSP.name<>('sa');

 

-- Scripting Out the Role Membership to Be Added

SELECT

'EXEC master..sp_addsrvrolemember @loginame = N'''+SL.name+''', @rolename = N'''+SR.name+'''

'AS[-- Server Roles the Logins Need to be Added --]

FROMmaster.sys.server_role_membersSRM

JOINmaster.sys.server_principalsSRONSR.principal_id=SRM.role_principal_id

JOINmaster.sys.server_principalsSLONSL.principal_id=SRM.member_principal_id

WHERESL.typeIN('S','G','U')

ANDSL.nameNOTLIKE'##%##'

ANDSL.nameNOTLIKE'NT AUTHORITY%'

ANDSL.nameNOTLIKE'NT SERVICE%'

ANDSL.name<>('sa');

 

 

-- Scripting out the Permissions to Be Granted

SELECT

CASEWHENSrvPerm.state_desc<>'GRANT_WITH_GRANT_OPTION'

THENSrvPerm.state_desc

ELSE'GRANT'

END

    +' '+SrvPerm.permission_name+' TO ['+SP.name+']'+

CASEWHENSrvPerm.state_desc<>'GRANT_WITH_GRANT_OPTION'

THEN''

ELSE' WITH GRANT OPTION'

ENDcollatedatabase_defaultAS[-- Server Level Permissions to Be Granted --]

FROMsys.server_permissionsASSrvPerm

JOINsys.server_principalsASSPONSrvPerm.grantee_principal_id=SP.principal_id

WHERE   SP.typeIN('S','U','G')

ANDSP.nameNOTLIKE'##%##'

ANDSP.nameNOTLIKE'NT AUTHORITY%'

ANDSP.nameNOTLIKE'NT SERVICE%'

ANDSP.name<>('sa');

 

SETNOCOUNTOFF