Recently we had a requirement to check who had a certain privilege across all of our Dynamics environments. The privilege in question was ‘Delete Change History‘ for Audit History records.

The ‘Delete Change History‘ role is found under Miscellaneous privileges, and the internal name is prvDeleteRecordChangeHistory. A full list of these can also be found on Microsoft Learn.

We need to find all of the Security Roles where this was enabled, and then the users who had this role. The interface to roles and privileges works but does not allow the searching we require.

SQL 4 CDS comes to the rescue yet again. I won’t go over the install procedure, but basically go to XrmToolBox, then download the SQL 4 CDS plugin.

Once it’s installed and you’ve connected to your Dynamics environment you can query the appropriate tables. There are several tables that hold the required information:
• systemuser
• role
• privilege
• roleprivileges

See examples below

Show all the users who have a specific privelege

SELECT
   systemuser.fullname AS UserName,
   systemuser.domainname,
   role.name AS RoleName,
   privilege.name AS PrivilegeName,
   privilege.accessright
FROM
   systemuserroles
JOIN
   systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
JOIN
   role ON systemuserroles.roleid = role.roleid
JOIN
   roleprivileges ON role.roleid = roleprivileges.roleid
JOIN
   privilege ON roleprivileges.privilegeid = privilege.privilegeid
WHERE
   privilege.name = 'prvDeleteRecordChangeHistory'
ORDER BY
   systemuser.fullname, role.name

Users who have System Administrator role

SELECT
   systemuser.fullname AS UserName,
   systemuser.domainname,
   role.name AS RoleName,
   accessmodename,*
FROM
   systemuserroles
JOIN
   systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
JOIN
   role ON systemuserroles.roleid = role.roleid
WHERE
   role.name = 'System Administrator' and azurestatename = 'Exists' and accessmodename <> 'Non-interactive' and isdisabled=0
ORDER BY
   systemuser.fullname, role.name

Hope this helps. 🙂

The Microsoft Dynamics Advanced Settings pages have been around for as long as I can remember – probably since Dynamics CRM 2011.

It’s been marked as deprecated for a number of years – but not totally gone yet. It used to be accessible from the top-right cog, and then ‘Advanced Settings’, but since Release Wave 2 this now takes you to the Power Platform Environment Settings

The issue is that some functionality isn’t available in the updated interface.

A prime example I found recently, is that viewing Processes in a Solution differs from the old and new interface.
The new interface (make.powerapps.com) shows Processes as below:

Which looks fine until you have many workflows and want to see the Primary Entity of a workflow.

See the older interface:

So why has this been removed from the new interface. Presumably there’s a reason for this.

Anyway, for now, we can still access the old interface by using the below URL

YOURORG.crm4.dynamics.com/main.aspx?settingsonly=true

That’ all for now 😀

Summary
As my background is in SQL Server databases I find it a lot easier to query data in SQL. The process in this post will show you how to carry out a manually run download of data from multiple objects in Salesforce or Dynamics CRM – using trial versions of KingswaySoft Tools in SSIS (SQL Server Integration Services).

I’ll be connecting to a trial Salesforce instance and downloading Contacts to SQL Server. Whenever I say Salesforce it can be interchanged with Dynamics CRM – the KingswaySoft CRM Components only slightly differ in how the objects and fields are selected.

I’ll assume that you have no prior knowledge of Visual Studio or SSIS, although you will need know how to connect to and run queries against a SQL database.

Requirements
You’ll need:
* A Salesforce or Dynamics CRM instance (this can be a trial)
* A Windows PC/Server with SSIS installed.
* A SQL Server instance (this can be hosted on a VM, or directly in Azure) with an empty database.

I’ll be using a VM running Windows 10 with the below items installed:
* SQL Server 2012 SP4
* Visual Studio 2015 Update 3
* SQL Server Data Tools for Visual Studio 2015
These are the versions I had had installed on my VM – newer versions of Visual Studio and SQL Server Data Tools should run fine, though I haven’t tested these.

Install KingswaySoft SSIS Integration Toolkit
We’ll install a trial version of this to allow us to connect to Salesforce. The only limitation that the trial version has is that you can only run it in the development environment. This means that the solution will not work if we deploy the SSIS package to a server – this will require a license.

Download links
SSIS Integration Toolkit for Salesforce –
https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-salesforce/download
SSIS Integration Toolkit for Microsoft Dynamics 365 –
https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download
On the download form KingswaySoft requires a corporate/business email.

If you haven’t got such an email you can use a temporary email from https://10minutemail.com/

Installation
Once you have the installation files just install the toolkit taking the default options.


Create A Visual Studio Project
In Visual Studio create a new project of type ‘Integration Service Project’

If this options isn’t available ensure that SSIS and SQL Server Data Tools are installed correctly.

Add and Configure Connection Managers
We will make two connections here – one to Salesforce, and the second to SQL Server.

Connection to Salesforce
Right-click on the “Connection Managers” area on the bottom of the screen and select “New Connection…”

In the next dialog select the “Connection Manager for Salesforce” then click “Add”

You will now need to choose the Instance Type, and enter your (Salesforce) User Name & Password, and Security Token.
The Security Token is used to ensure that if someone has your Salesforce account details they can’t connect to the Salesforce data connector without it.
If you know your Security Token then enter it, otherwise request a new Token. This is the Salesforce Help Page for Security Tokens – https://help.salesforce.com/articleView?id=sf.user_security_token.htm&type=5
Click on “Test Connection” and you should see a message stating that the Test Connection Succeeded. If not, check the details you have entered.

Connection to SQL Server
Right-click on the “Connection Managers” area on the bottom of the screen and select “New OLE DB Connection…”

On the next dialog click “New” to make a new data connection, then enter the details for your SQL Server connection.
My connection is shown below, connecting to the local SQL Server (this is the dot in the Server Name) using SQL Server authentication and a database called “SFDB-Blog”
Click on “Test Connection” and you should see a message stating that the Test Connection Succeeded. If not, check the details you have entered.
Click on “OK” to close the Connection Manager dialog

You should now have 2 connections in the “Connection Managers”. Don’t worry about what they’re named – that’s not important.

CREATING THE SALESFORCE SOURCE COMPONENT
From the SSIS Toolbox drag a “Data Flow Task” to the package

Right-click on the Data Flow Task and rename it to “DFT – Download Contacts”
Double-click on “DFT – Download Contacts” to edit it. You will now be in the Data Flow edit screen:

From the SSIS Toolbox drag a “Salesforce Source” component to the Data Flow

Double-click the newly added “Salesforce Source”. Click on the Connection Manager dropdown and select the value Salesforce Connection Manager.
For the Source Object select the object you want to download. I will be downloading the Contact object.

Click on the “Columns” on the left and select the fields you want to download. As a general rule of thumb it’s better to download only the fields you want – but always download the Id column (the record unique identifier). You may want to click on the top check box to uncheck all fields initially.

Click the ‘OK’ button to close the dialog

CREATING THE SQL DESTINATION COMPONENT
From the SSIS Toolbox drag a “OLE DB Destination” component to the Data Flow. Rename the newly added “OLE DB Destination” to “OLEDB – Contacts”.
Click on “Salesforce Source” component, and then drag the connector arrow to the “OLEDB – Contacts” component. It should now look as below:

Double-click “OLEDB – Contacts” to open the edit dialog.
We will now use the component to generate the SQL to create the SQL Server table to hold the data. Click the “New button” and the SQL will be displayed:

Copy this SQL and paste it into a connection to your SQL Server – I’m using SSMS (SQL Server Management Studio). In the script change the text “OLEDB – Contacts” to the name you want to give to the SQL table – I’ll be using “Contacts”. Execute the query to create the SQL table.

Back in Visual Studio – Cancel the open script dialog, and in the main dialog on the table dropdown select your new table.

Click on the ‘Mappings” on the left to ensure that all columns are mapped correctly – it should look similar to below where all columns are connected from left to right:

Click “OK” to close the dialog.

FIRST RUN OF DOWNLOADING DATA
We are now ready to run and test the first table downloading data from Salesforce.
Click the “Start” button

After a few seconds (or minutes depending on the number of records in your Salesforce data object) it should complete successfully:

To ensure it has worked correctly view the data in your SQL table:

CLEAR DATA BEFORE RUN
When the routine runs we want to clear the existing SQL data otherwise it would append duplicate data to the SQL table.
To do this click on the “Control Flow”, and drag an “Execute SQL Task” component to the main pane, above the component “DFT – Download Contacts”:

Right-click on the newly added “Execute SQL Task” and rename it to “SQL – Truncate Contacts”
Double-click on ” SQL – Truncate Contacts” to edit it. For the “Connection” select the SQL connection, and in the “SQLStatement” enter as below (using your SQL table name):

Click OK to close the dialog, and as then connect the components by dragging the arrow from “Execute SQL Task” to “DFT – Download Contacts”
Click on the “Start” button to run the routine and once complete it should look as below.


ADDING MORE DOWNLOAD TABLES
You can repeat the above process to add additional tables. I’ve added the Account table – as shown below:

WRAP UP
I hope you found this post beneficial – Thanks for reading 🙂

Photo by Markus Spiske on Unsplash