web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Need SQL query or view...
Microsoft Dynamics CRM (Archived)

Need SQL query or view to find privilege

(0) ShareShare
ReportReport
Posted on by Abdul Wahab 12,119 Moderator

Hi all

I need to find which security role has merge privilege. Is there any query or advanced find view which makes me help to retrieve this kind of information.

Thank you

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Nithya Gopinath Profile Picture
    Nithya Gopinath 17,078 on at
    RE: Need SQL query or view to find privilege

    Hi Abdul Wahab,

    Try the following query.

    SELECT  DISTINCT
        r.Name
        ,COALESCE(e.OriginalLocalizedName, e.Name) AS [EntityName]
        ,CASE p.AccessRight
             WHEN 32     THEN 'Create' /* or hex value 0x20*/
             WHEN 1      THEN 'Read'
             WHEN 2      THEN 'Write'
             WHEN 65536  THEN 'Delete' /* or hex value 0x10000*/
             WHEN 4      THEN 'Append'
             WHEN 16     THEN 'AppendTo'
             WHEN 524288 THEN 'Assign' /* or hex value 0x80000*/
             WHEN 262144 THEN 'Share' /* or hex value 0x40000*/
             ELSE 'None'
        END AS [Privilege]
        ,CASE (rp.PrivilegeDepthMask % 0x0F)
             WHEN 1 THEN 'User (Basic)'
             WHEN 2 THEN 'Business Unit (Local)'
             WHEN 4 THEN 'Parental (Deep)'
             WHEN 8 THEN 'Organization (Global)'
             ELSE 'Unknown'
        END AS [PrivilegeLevel]
        ,(rp.PrivilegeDepthMask % 0x0F) as [PrivilegeDepthMask]
        ,CASE WHEN e.IsCustomEntity = 1 THEN 'Yes' ELSE 'No' END AS [IsCustomEntity]
FROM    Role AS r
INNER   JOIN RolePrivileges AS rp 
        ON r.RoleId = rp.RoleId
INNER   JOIN Privilege AS p 
        ON rp.PrivilegeId = p.PrivilegeId
INNER   JOIN PrivilegeObjectTypeCodes AS potc 
        ON potc.PrivilegeId = p.PrivilegeId
INNER   JOIN MetadataSchema.Entity AS e
        ON e.ObjectTypeCode = potc.ObjectTypeCode
ORDER   BY r.Name, [EntityName]

    Please go through the following link for Dynamics CRM 2011 Security Role SQL query.

    stackoverflow.com/.../dynamics-crm-2011-security-role-sql-query

  • Verified answer
    tw0sh3ds Profile Picture
    tw0sh3ds 5,600 on at
    RE: Need SQL query or view to find privilege

    SELECT r.Name FROM Privilege p

    JOIN RolePrivileges rp on p.PrivilegeId = rp.PrivilegeId

    JOIN Role r on r.RoleId = rp.RoleId

    where p.Name = 'prvMerge'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
HR-09070029-0 Profile Picture

HR-09070029-0 2

#1
UllrSki Profile Picture

UllrSki 2

#3
ED-30091530-0 Profile Picture

ED-30091530-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans