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

(0) Share

Report

Posted on by Sumit Ruhela

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)

All responses (14)

Answers (2)

Verified answer

Aileen Gusni 44,524 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Sumit Ruhela 1,805 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Aileen Gusni 44,524 on at

Like (0)

Report

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()

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

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 :)

Was this reply helpful? Yes No
Sumit Ruhela 1,805 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Sumit Ruhela 1,805 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Sumit Ruhela 1,805 on at

Like (0)

Report

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.

Was this reply helpful? Yes No