Bill Baer

Senior Product Marketing Manager (SharePoint), Microsoft Certified Master for SharePoint, Microsoft Corporation

Simple Query to Audit Site Collection/Web Access Levels

Simple Query to Audit Site Collection/Web Access Levels

  • Comments 2
  • Likes

USE SELECT DISTINCT t1.siteID,t2.webID, t2.title [accessLevel],t3.tp_login,CONVERT(DECIMAL(6,2),((CONVERT(DECIMAL,t4.diskUsed)/1024)/1024)) [diskUsed MB], t6.fullUrlFROM DBO.RoleAssignment t1 JOIN DBO.roles t2 ON t2.siteID = t1.siteID AND t2.roleID = t1.roleID AND t2.roleID = 1073741829 JOIN DBO.userInfo t3 ON t3.tp_siteID = t1.siteID AND t3.tp_id = t1.principalID JOIN DBO.sites t4 ON t4.[id] = t1.siteID JOIN DBO.perms t5 ON t5.siteID = t1.siteID AND t5.scopeID = t1.scopeID JOIN webs t6 ON t6.[id] = t5.webID

  • Awesome tip!! Would like to see that built into a special Feature for Central Admin.

  • What is the ScopeId column?

    During a content DB upgrade from SPS 2003 to MOSS 2007, we're getting the following error:

    [SPContentDatabaseSequence] [ERROR] [8/16/2007 5:04:01 PM]: Action of Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence failed.

    [SPContentDatabaseSequence] [ERROR] [8/16/2007 5:04:01 PM]: Cannot insert the value NULL into column 'ScopeId', table 'portail1_WSS_SITE.dbo.Webs'; column does not allow nulls. UPDATE fails.

    [SPContentDatabaseSequence] [ERROR] [8/16/2007 5:04:01 PM]:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

      at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

      at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

      at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)

      at Microsoft.SharePoint.Upgrade.SPDatabaseSequence.ExecuteDataDefinitionMethodCore(SqlSession sqlSession, ISqlSession isqlSession, String sqlscript, SPSqlCommandFactory sqlcmdFactory, String[] strTables, Int32[] nThroughputs, SPLog logGlobal)

      at Microsoft.SharePoint.Upgrade.SPDatabaseWssAction.ExecuteDataDefinitionMethod(ISqlSession sqlSession, String sqlscript)

      at Microsoft.SharePoint.Upgrade.SecurityUpgrade4.Upgrade()

      at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade()

    [SecurityUpgrade4] [] [DEBUG] [8/16/2007 5:04:01 PM]: Begin Rollback()

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment