SQL to list all assets Group has Edit permissions for
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
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
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...
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.
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.
4 Posted by Michael Patrick on 18 May, 2010 06:20 PM
The code got mangled in previous post...attaching file.
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.
Tim closed this discussion on 18 May, 2010 06:25 PM.