Useful CRM Tips

Useful CRM Tips...

Monday, March 24, 2014

SQL Script which gives the list of users who never logged into CRM

Below is the SQL Script which gives the list of users who never logged into CRM with given Organisation DB.


Use MSCRM_CONFIG

SELECT

O.FriendlyName as Organization, SUO.LastAccessTime, U.FirstName, U.LastName, U.FullName

FROM

SystemUserOrganizations SUO

LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId AND LEFT(AuthInfo, 1) = 'c'

LEFT JOIN Organization O ON SUO.OrganizationId=O.Id

Inner Join standard_MSCRM.dbo.SystemUser U On  SUO.CrmUserId = U.SystemUserId

WHERE

LastAccessTime Is Null  

Wednesday, February 26, 2014

SQL Query to Retrieve All Security Roles Have Which are all Permissions on which Entities

The following SQL statement will retrieve a list of security roles and the level of access each security role has in regards to entities. this will be useful for scenarios when you want to see who has "Delete" permissions on "Contacts" for instance.
Simply run the select query against the CRM database
SELECT DISTINCT
FilteredRole.name,
EntityView.PhysicalName AS [Entity Name],
CASE Privilege.AccessRight
WHEN 1 THEN 'READ'
WHEN 2 THEN 'WRITE'
WHEN 4 THEN 'APPEND'
WHEN 16 THEN 'APPENDTO'
WHEN 32 THEN 'CREATE'
WHEN 65536 THEN 'DELETE'
WHEN 262144 THEN 'SHARE'
WHEN 524288 THEN 'ASSIGN'
END AS [Access Level],
CASE PrivilegeDepthMask
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organisation'
END AS [Security Level]
FROM  RolePrivileges
INNER JOIN
FilteredRole ON RolePrivileges.RoleId = FilteredRole.roleid
INNER JOIN
PrivilegeObjectTypeCodes ON RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId
INNER JOIN
Privilege ON RolePrivileges.PrivilegeId = Privilege.PrivilegeId
INNER JOIN
EntityView ON EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode
ORDER BY FilteredRole.name, [Entity Name]