tag:help-archives.hannonhill.com,2010-02-09:/discussions/how-do-i/22-sql-to-list-all-assets-group-has-edit-permissions-forCascade CMS: Discussion 2011-04-07T22:11:52Ztag:help-archives.hannonhill.com,2010-02-09:Comment/16515902010-05-11T19:06:15Z2010-05-11T19:06:16ZSQL to list all assets Group has Edit permissions for<div><p>This should probably be moved to 'How do I...'</p>
<p>I wanted to post a followup question though. I have a group
called 'Admissions' which has been assigned 'Write' access to a
folder. I thought if I ran the query below I would be able to see a
record for those permissions:</p>
<p>SELECT * FROM [contentxml].[dbo].[cxml_permissions]<br>
WHERE groupName LIKE '%admi%'</p>
<p>However this results in no results...</p></div>Michael Patricktag:help-archives.hannonhill.com,2010-02-09:Comment/16515902010-05-12T18:05:28Z2010-05-12T18:05:28ZSQL to list all assets Group has Edit permissions for<div><p>Hi Michael,</p>
<p>With the way permissions are stored in the database, this likely
would not be as simple as one query. Instead, I believe a script
would need to be written which would query recursively. It is
fairly simple to find which assets are readable/writable for a
certain group or user <em>if the access was set explicitly for
those given assets</em>. However, it sounds like you are looking to
see which assets are readable/writable underneath a certain folder
to which the user/group has access (likely meaning that the assets
underneath inherited the permissions from the parent). Is this
correct? If so, we may be able to provide some tips on writing some
sort of script to do this.</p></div>Timtag:help-archives.hannonhill.com,2010-02-09:Comment/16515902010-05-18T18:18:55Z2010-05-18T18:25:53ZSQL to list all assets Group has Edit permissions for<div><p>This is what we came up with. It works well for us...maybe it
will help someone else out there.</p>
<pre>
<code>CREATE PROCEDURE My_gsp
@like_groupname NVARCHAR(255) = NULL
AS
--exec my_gsp '%Adv%'
WITH get_stuff_perms(id,parentfolderid,name,displayname,cachepath,groupname)
AS (SELECT fc.id,
fc.parentfolderid,
fc.name,
fc.displayname,
fc.cachepath,
a.groupname
FROM dbo.cxml_foldercontent fc
LEFT JOIN dbo.cxml_permissions p
ON fc.permissionsid = p.id
LEFT JOIN dbo.cxml_aclentry a
ON p.id = a.permissionsid
WHERE a.groupname LIKE @like_groupname
UNION ALL
SELECT rfc.id,
rfc.parentfolderid,
rfc.name,
rfc.displayname,
rfc.cachepath,
gsp.groupname
FROM dbo.cxml_foldercontent rfc
INNER JOIN get_stuff_perms gsp
ON rfc.parentfolderid = gsp.id)
SELECT *
FROM get_stuff_perms
RETURN (0)
GO</code>
</pre></div>Michael Patricktag:help-archives.hannonhill.com,2010-02-09:Comment/16515902010-05-18T18:20:53Z2010-05-18T18:20:57ZSQL to list all assets Group has Edit permissions for<div><p>The code got mangled in previous post...attaching file.</p></div>Michael Patricktag:help-archives.hannonhill.com,2010-02-09:Comment/16515902010-05-18T18:25:40Z2010-05-18T18:25:40ZSQL to list all assets Group has Edit permissions for<div><p>Thanks for sharing Michael! Also, I edited your post so that the
code can be seen on this page.<br></p></div>Tim