Home → How Do I... →

SQL to list all assets Group has Edit permissions for

• Edit

Michael Patrick

10 May, 2010 11:37 PM

I am looking for a SQL statement that could list all assets the a Group has been assigned permissions to.

For instance say 'GroupA' was granted 'Write' access on 'Folder1' & 'Folder2'. The sql statement would use the Group name 'GroupA' to return all assets under the folders 'Folder1' & 'Folder2' for which the group has write access.

Thanks for any help,
Michael

1. 1 Posted by Michael Patrick on 11 May, 2010 07:06 PM

   

   This should probably be moved to 'How do I...'

   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:

   SELECT * FROM [contentxml].[dbo].[cxml_permissions]
   WHERE groupName LIKE '%admi%'

   However this results in no results...

   • Edit

2. Support Staff 2 Posted by Tim on 12 May, 2010 06:05 PM

   

   Hi Michael,

   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 if the access was set explicitly for those given assets. 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.

   • Edit

3. 3 Posted by Michael Patrick on 18 May, 2010 06:18 PM

   

   This is what we came up with. It works well for us...maybe it will help someone else out there.

   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
   

   • Edit

4. 4 Posted by Michael Patrick on 18 May, 2010 06:20 PM

   

   The code got mangled in previous post...attaching file.

   • assets_by_group.txt 1.07 KB
   • Edit

5. Support Staff 5 Posted by Tim on 18 May, 2010 06:25 PM

   

   Thanks for sharing Michael! Also, I edited your post so that the code can be seen on this page.
   

   • Edit

6. Tim closed this discussion on 18 May, 2010 06:25 PM.