shaneoss

Site Owner Groups and Membership

Blog Post created by shaneoss on Mar 27, 2017

Ever needed to contact all Site Owners within SharePoint for some reason, maintenance or otherwise? It can be a difficult task if you have lot's of site collections with various owners, all managing their own sites.

 

I found this SQL script from Patrick which i'd modified (attached) to work with SharePoint 2013/2016 content DBs.

https://patrickboom.wordpress.com/2010/08/05/gathering-site-data-using-sql/

 

I also created a workflow to iterate through all of the content DBs to get the membership of the Site Owner groups within the site collections. I created a Site Workflow which is then scheduled to run when required.

 

Requirements:

You'll need a user account who can query sys.databases and your content DBs. I used a SharePoint services account and just granted the SQL serveradmin role to the account.

You'll also need nicely named Content DBs. This solution works on the assumption that your Content DB names contain the word "content" e.g. WSS_Content_IT or something.

 

1. Using the list template attached. Import the template to the list template gallery and create a new list for storing the owner information pulled from the content DBs.

2. Create a new Site workflow using Nintex and import the attached workflow.

3. Update the SQL connection string in the Execute SQL actions and set the user account to the SQL account you'd granted the serveradmin role.

4. Update the Query List action to point to the new list you'd created in Step 1. Update the filter to "Select items only when the following is true": Title is equal to SiteTitle (Workflow variable) AND Content DB is equal to ContentDB (Workflow variable). Select ID and URL fields in the Outputs and store the retrieved values in ExistingItemID and ExistingURL.

Update Query Action

Query List Outputs

5. Update the Create List Item action to create a new item in the list you created in Step 1. Add the fields, Title, Content DB, URL, Sub Webs, Owner Email Address and map them to the following Workflow variables SiteTitle, ContentDB, FullUrl, NrWebs, OwnerEmail as per the image below.

Update Create List Item Action

6. Update the Update List Item action to update items in the list you created in Step 1. Add the fields, Title, Content DB, URL, Sub Webs, Owner Email Address and map them to the following Workflow variables SiteTitle, ContentDB, FullUrl, NrWebs, OwnerEmail as per the image below.

Update the Update List Item Action

7. Save and Publish the site workflow.

8. Add a Schedule for the newly created site workflow.

 

After the initial run of the scheduled site workflow. Check the list you created in Step 1 to see if the list has been populated with the Site Owner information from the Content DBs.

 

Update (27/7/17):

To ensure we don't get any database snapshots. Modify the first execute SQL action (Get Content DBs) and add "AND source_database_id IS NULL" to the SQL query.

E.g.

select name
 from sys.databases
where (name like '%content%') AND (name <> 'Content_MySites') AND (name <> 'Content_CentralAdmin') AND (source_database_id IS NULL)

 

Update (28/08/18)

I've simplified the SQL script to collate the data from the content DBs.

Just update the Execute SQL action for Getting Site Collection data from Content DBs to the following. It can also be run directly against your content DBs. If running directly against a content DB just change {WorkflowVariable:ContentDB} to the name of your Content DB.

 

USE {WorkflowVariable:ContentDB};

WITH Owners AS(

SELECT DISTINCT W.Title, U.TP_EMAIL AS Email

FROM AllSites S

INNER JOIN GroupMembership GM ON GM.SiteId = S.Id AND S.Deleted = 0

INNER JOIN UserInfo U on U.tp_ID = GM.MemberId AND U.tp_SiteID = S.Id

INNER JOIN Groups G ON G.ID = GM.GroupId AND G.Title LIKE '%OWNER%' AND G.SiteId = S.Id

INNER JOIN AllWebs W ON W.Id = S.RootWebId AND W.SiteId = S.Id

WHERE TP_EMAIL <> '')

,

SiteCounter

AS(

SELECT SiteId, Count(*) AS NrWebs FROM AllWebs INNER JOIN AllSites ON AllSites.Id = AllWebs.SiteId AND AllSites.Deleted = 0 GROUP BY SiteId

) 

SELECT

Title,

FullUrl,

C.NrWebs,

(SELECT Email +'; '

FROM Owners O

WHERE O.Title = W.Title

FOR XML PATH('')) [OwnersEmail]

FROM AllWebs W

INNER JOIN SiteCounter C ON C.SiteId = W.SiteId

GROUP BY W.Title, W.FullUrl, C.NrWebs,ParentWebId

Having ParentWebId IS NULL

Outcomes