Query to find all views/charts owned or shared with a user in Dynamics 2013

Solution 1:

@Ryan 's solution helped me quite a bit but was painful to transcribe. This is the solution in a more usable format (with minor edits):

DECLARE @userid varchar(100)
SELECT @userid = 'domain\user'

-- Get team membership for the user
IF OBJECT_ID(N'tempdb.dbo.#UserAndTeams') IS NOT NULL DROP TABLE #UserAndTeams

SELECT DISTINCT t.TeamId TeamOrUserId, t.[Name]
INTO #UserAndTeams
FROM
 TeamBase t
  INNER JOIN TeamMembership tm ON t.TeamId = tm.TeamId
  INNER JOIN SystemUserBase su ON su.SystemUserId = tm.SystemUserId
WHERE
 su.DomainName = @userid

INSERT INTO #UserAndTeams(TeamOrUserId, [Name])
SELECT su.SystemUserId, su.Firstname + ' ' + su.Lastname [Name]
FROM SystemUserBase su WHERE su.DomainName = @userid

-- Get a union of all teams and this user
IF OBJECT_ID(N'tempdb.dbo.#AllUsersAndTeams') IS NOT NULL DROP TABLE #AllUsersAndTeams

SELECT t.TeamId TeamOrUserId, t.[Name] 
INTO #AllUsersAndTeams
FROM TeamBase t

INSERT INTO #AllUsersAndTeams(TeamOrUserId, [Name])
SELECT su.SystemUserId, su.Firstname + ' ' + su.Lastname [Name]
FROM SystemUserBase su

-- Extract share info from POA for selected entity types
SELECT DISTINCT su.[Name] SharedWith, objectIds.[Type], objectIds.[Name] Objectname, aut.[Name] Ownername
FROM
 PrincipalobjectAccess poa WITH (NOLOCK)
  INNER JOIN #UserAndTeams su WITH (NOLOCK) ON poa.PrincipalId = su.TeamOrUserId
  INNER JOIN
    (
      SELECT 'Chart' [Type], UserQueryVisualizationId id, [Name], OwnerId FROM UserQueryVisualizationBase WITH (NOLOCK)
      UNION SELECT 'View', UserQueryId id, [Name], OwnerId FROM UserQueryBase WITH (NOLOCK)
      UNION SELECT 'Report', ReportId, [Name], OwnerId FROM Report WITH (NOLOCK)
      UNION SELECT 'Dashboard', uf.userFormId id, [Name], OwnerId FROM UserForm uf WITH (NOLOCK) WHERE uf.[Type] = 0
    ) objectIds ON poa.ObjectId = objectIds.id
  INNER JOIN #AllUsersAndTeams aut ON objectIds.OwnerId = aut.TeamOrUserId
ORDER BY
 objectIds.[Type], objectIds.[Name]

Solution 2:

I came up with this procedure:

Which works well for views/dashboards and charts. If a user can access an item via team membership the team name is displayed; if it's been shared directly with the user, the users' name is displayed.

It needs to be run for a user with access to the base tables.

(I can't post the full SQL to Stack Overflow! It's containted in the attached image.)