SQL Scripts

List of all checked out files for an web applications:

SELECT
    sites.FullUrl AS SiteURL, tp_DirName AS Directory,  tp_LeafName AS Document,  [tp_CheckoutUserId] AS UserID

FROM 
[cms-roche.dev].[dbo].[AllUserData]

INNER JOIN 
[cms-roche.dev].dbo.[Sites] on sites.id=alluserdata.tp_SiteId

WHERE
[tp_CheckoutUserId]<>0

 

List of all activated Features for a web applciation:

SELECT 
sites.FullUrl AS SiteCollection,  Webs.FullUrl AS Site,  Features.FeatureId

FROM 
[cms-roche.dev].[dbo].[Features]

INNER JOIN 
[cms-roche.dev].dbo.Sites ON Features.SiteId=sites.Id

INNER JOIN 
[cms-roche.dev].dbo.Webs ON Features.WebId=Webs.Id

WHERE 
Features.FeatureId='00BFEA71-3A1D-41D3-A0EE-651D11570120'

List of all user/group rights for a SharePoint web application

The following T-SQL command will list all user/group rights defined for all site collection of web application

-- Query to get all the users assigned to roles
SELECT DISTINCT
CASE WHEN PATINDEX('%\%', FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX('%\%', FullUrl) - 1) ELSE FullUrl END AS [Site],
Webs.Title,
Webs.FullUrl,
Perms.ScopeUrl,
UserInfo.tp_Login As Account,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],
NULL AS [SharePoint Group],
Roles.Title AS RoleTitle,
Roles.PermMask
FROM
dbo.RoleAssignment
INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID AND UserInfo.tp_ID = RoleAssignment.PrincipalId
INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId
INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId
INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id
WHERE
Roles.Type<>1 AND tp_Deleted=0
UNION
-- Query to get all the SharePoint groups assigned to roles
SELECT DISTINCT
CASE WHEN PATINDEX('%\%', FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX('%\%', FullUrl) - 1) ELSE FullUrl END AS [Site],
Webs.Title,
Webs.FullUrl,
Perms.ScopeUrl,
UserInfo.tp_Login As Account,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,
CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],
Groups.Title AS [SharePoint Group],
Roles.Title AS RoleTitle,
Roles.PermMask
FROM
dbo.RoleAssignment
INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId
INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId
INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id
INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId AND RoleAssignment.PrincipalId = Groups.ID
INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID
INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID AND GroupMembership.MemberId = UserInfo.tp_ID
WHERE
Roles.Type<>1 AND tp_Deleted=0

SharePoint Group Membership for specific site

I you want to get a list of all groups and the members in a site collection you can use this sql script.

Replace the SITE GUID with the guid of site collection for which you want this list

SELECT

tp_title AS [User name],

tp_Email as [Mail address],

Title as [SharePoint group]

FROM

GroupMembership

INNER JOIN UserInfo ON GroupMembership.MemberID=UserInfo.tp_ID and GroupMembership.SiteID=UserInfo.tp_SiteID

INNER JOIN Groups ON GroupMembership.GroupID=Groups.ID and GroupMembership.SiteID=Groups.SiteId

WHERE

GroupMembership.SiteID='SITE GUID'