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

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics CRM (Archived)

To find the list of users in Field security profile in SQL database

(0) ShareShare
ReportReport
Posted on by 1,805

Hi All,

I have been working on this since past one week but there is no luck. I am trying to find the list of users which are a member of field security profile "system administrator" in SQL database. I am not able to find that table which contains the mapping of users with the above mentioned field security profile. can someone please help?

for your knowledge, i have attached the image file

as you can, i have four users in system administrator FSP. Where do i find this database table in SQL?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Aileen Gusni Profile Picture
    44,524 on at

    Hi, try this Query to list all of the user under the System Administrator field level security profile:

    select * from SystemUser
    where SystemUserId in
    (
    select SystemUserId from SystemUserProfiles
    where FieldSecurityProfileId in
    (
    select FieldSecurityProfileId from FieldSecurityProfile
    where Description = 'System Administrator'
    )
    )

    Hope it helps!

  • Sumit Ruhela Profile Picture
    1,805 on at

    Hi Aileen,

    Thanx for your valuable input. It worked. Now in addition to this, lets say i want to add a user to this system administrator profile. what query i should run?

  • Aileen Gusni Profile Picture
    44,524 on at

    Hi Sumit,

    Nice to know it works.

    I am not sure if using the unsupported customization (update the database), but I think you can add data to this table :

    "SystemUserProfiles"

    This table is the entity to store N:N relationship between SystemUser and FieldSecurityProfile

    Which is it is only having 4 columns:

    why you dont use the UI?

    Is that any issue? or error? Maybe I can help to take a look?

  • Community Member Profile Picture
    on at

    Hi,

    By default, users having system administrator security role are members of  system administrator profile.

    You can not update or delete system created system administrator profile. If you want to add users to that profile then you will have to give system admin role to them.

    You can create new profile and use that.

    Hope it helps!!

  • Verified answer
    Aileen Gusni Profile Picture
    44,524 on at

    Hi,

    Maybe this syntax can help you, but my suggestion since this is CRM On Premise, you should backup

    INSERT INTO [Contoso].[dbo].[SystemUserProfiles]

              ([SystemUserId]

              ,[FieldSecurityProfileId]

              ,[SystemUserProfileId])

        VALUES

              (<SystemUserId, uniqueidentifier,>

              ,<FieldSecurityProfileId, uniqueidentifier,>

              ,<SystemUserProfileId, uniqueidentifier,>)

    GO

    change the values:

    VALUES

              (<SystemUserId, uniqueidentifier,>

              ,<FieldSecurityProfileId, uniqueidentifier,>

              ,<SystemUserProfileId, uniqueidentifier,>)

    to the system userid and feildsecurityprofile id you want.

    and the systemuserprofileid it should be you replace with:

    NEWID()

  • Aileen Gusni Profile Picture
    44,524 on at

    I hope you can still achieve it using CRM UI, not from database, I just help you to do SQL Query to list all of the users since you asked it how to list :)

  • Sumit Ruhela Profile Picture
    1,805 on at

    i tried adding the user through UI but got the following error 

    and when and i see the log file, this is what i get

    Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Caller bb6d4cf3-51c9-e311-9d84-001e6783d485 doesn't have privilege for attribute 'new_password' of entity 'account'Detail:
    <OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
    <ErrorCode>-2147220906</ErrorCode>
    <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Caller bb6d4cf3-51c9-e311-9d84-001e6783d485 doesn't have privilege for attribute 'new_password' of entity 'account'</Message>
    <Timestamp>2014-06-26T13:40:49.9396928Z</Timestamp>
    <InnerFault i:nil="true" />
    <TraceText i:nil="true" />
    </OrganizationServiceFault>

    if we can sort out this problem then there is no need to add the user from sql.

  • Aileen Gusni Profile Picture
    44,524 on at

    Caller bb6d4cf3-51c9-e311-9d84-001e6783d485 doesn't have privilege for attribute 'new_password' of entity 'account'

    It means that "bb6d4cf3-51c9-e311-9d84-001e6783d485" does not have privilege (can be read, write, create) new_password in Account entity.

    Okay, let me ask you, this user, bb6d4cf3-51c9-e311-9d84-001e6783d485 what is the position?

    Can you check whether this user have enough security or not to access Account?

  • Sumit Ruhela Profile Picture
    1,805 on at

    Hi Aileen,

    This user is default admin which has been added to that field security profile from sql database. but even now, when i tried to add or delete users from UI interface in field security profile, it gives the same error message that this user doesnt have privilege for attribute 'new_password' of entity 'account'.

    How can we give this privilege through UI interface?

  • Sumit Ruhela Profile Picture
    1,805 on at

    i have attached this file

    if you notice, i have given all permissions to that user. you can see in the first row that new_password has all rights. is there any other place where we need to give the access of filed security profile to the users.

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans