SQL queries for Sharepoint 2010 Site and User Permissions

 Computer, Sharepoint, Software  Comments Off on SQL queries for Sharepoint 2010 Site and User Permissions
Nov 142013
 
Share

In some cases you may want to audit who has what permissions to various parts of your SharePoint site or farm.

This query will give you a list of all of your groups, the roles / permissions of the groups, the users in those groups, and what they have permissions to:

select ra.PrincipalId as 'GroupID'
, g.Title 'GroupTitle'
, p.ScopeUrl
, r.Title 'RoleTitle'
, ui.tp_Title 'UserOrADgroup' /* Comment out to exclude individual users */
from RoleAssignment ra with (nolock)
     left outer join Roles r  with (nolock) on ra.RoleId = r.RoleId
     left outer join Perms p  with (nolock) on ra.ScopeId = p.ScopeId
     left outer join GroupMembership gm  with (nolock) on ra.PrincipalId = gm.GroupId /* Comment out to exclude individual users */
   left outer join Groups g  with (nolock) on gm.GroupId = g.ID
--   left outer join Groups g  with (nolock) on ra.PrincipalId = g.ID /* Use if only querying for groups */
     left outer join UserInfo ui  with (nolock) on gm.MemberId = ui.tp_ID /* Comment out to exclude individual users */
where  p.ScopeUrl like '%' AND tp_Title not like 'NULL' AND tp_Title not like 'System Account' AND p.ScopeUrl not like '_c%'
group by g.Title, P.ScopeUrl, ra.PrincipalId, r.Title order by p.ScopeUrl, g.Title

To get all Groups and their membership:

SELECT dbo.Groups.ID
, dbo.Groups.Title
, dbo.UserInfo.tp_Title
, dbo.UserInfo.tp_Login 
FROM dbo.GroupMembership 
INNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId 
INNER JOIN dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID 
Order by Title

Other user Sharepoint Content Database Links: http://www.demantprasad.com/search/label/Useful%20SQL%20Queries%20for%20SharePoint%20Content%20Database

Manage all SSRS Subscriptions in Sharepoint

 Computer, Sharepoint, Software  Comments Off on Manage all SSRS Subscriptions in Sharepoint
Oct 042013
 
Share

I needed a way for me and my teammates to see and edit all report subscriptions across a Sharepoint site
with SQL Reporting Services in Integrated mode.

There are two parts here; the first is a function that returns text between delimiters. This is important to get
the site and folder from the ItemPath in the ReportServer’s Catalog table.

This function takes a string, delimiter and position. For example
dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,2)
Will Return the SubSite as this is the 2nd item delimited.

dbo.fn_ParseStr(‘Server/SubSite/Folder’,’/’,3)
Will Return the Folder as it is the 3rd item delimited.

create Function dbo.fn_ParseStr(@Message Varchar(1000), @delimiter char(1), @index int )
Returns Varchar(1000)
As
Begin
Declare
@curIndex int = 0,
@pos int = 1,
@prevPos int = 0,
@result varchar(1000)
while @pos > 0
Begin
set @pos = CHARINDEX(@delimiter, @Message, @prevPos);
if(@pos > 0)
begin-- get the chars between the prev position to next delimiter pos
set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos)
end
else
begin--get last delim message
set @result = SUBSTRING(@message, @prevPos, LEN(@message))
end
if(@index = @curIndex)
begin
return @result
end
set @prevPos = @pos + 1
set @curIndex = @curIndex + 1;
end
return ''--not found
End

Here is the query to return the fields for the report and also builds the link to edit the subscription.

USE [ReportServer]
GO
SELECT
distinct(S.[SubscriptionID]),
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[LastRunTime],
C.ItemID as CatalogID,
--- This substring removes the GUID from the Path in the Catalog table.
substring(C.[Path], 40, 255) as Path ,
dbo.fn_ParseStr(C.[Path],'/',2) as Site,
dbo.fn_ParseStr(C.[Path],'/',3) as Folder,
C.[Name],
[U1].[UserName] AS [Owner],
[URL2] = 'https://yourservername/'+ dbo.fn_ParseStr(C.[Path],'/',2) +
'/_layouts/ReportServer/SPSubscriptionProperties.aspx?SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) +
'&list=' + CAST(F.[tp_ID] AS VARCHAR(80)) + '&ID=' + CAST(D.[DoclibRowId] AS VARCHAR(80))
FROM
ReportServer.dbo.[Subscriptions] S
JOIN ReportServer.dbo.[Catalog] C ON S.[Report_OID] = C.[ItemID]
JOIN ReportServer.dbo.[Users] [U1] ON S.[OwnerID] = [U1].[UserID]
-- Your Sharepoint Content Database
JOIN WSS_Content_.dbo.AllDocs D on D.Id = C.[ItemID]
JOIN WSS_Content_.dbo.Lists F on F.tp_ID = D.ListId