SQL to list all assets Group has Edit permissions for

Michael Patrick's Avatar

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

    Michael Patrick's Avatar

    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...

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

    Tim's Avatar

    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.

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

    Michael Patrick's Avatar

    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
    
  4. 4 Posted by Michael Patrick on 18 May, 2010 06:20 PM

    Michael Patrick's Avatar

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

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

    Tim's Avatar

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

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

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac