On-Premise Dynamics 365 – How does Deployment Manager find all available databases?

Today I was working with a Dynamics 365 instance where I did not have full SA access to the SQL Server instance. My deployment administrator account was able to connect to Deployment Manager ok, disable organizations ok and delete Organizations, however, when I tried to re-import an organization I had already deleted, I got the below error.

“The Import Organization Wizard cannot connect to the SQL Server or cannot find an organization database on <InstanceName>. Verify that the server name in the SQL Server box is correct and that the computer is running Microsoft SQL Server when an organization database is stored.”

I remember from previous experience that Deployment Administrator connects to the SQL Server and looks for a specific table, but rather than guessing permissions, wanted to furnish the DBA with exactly what was required.

So I ran a SQL trace using SQL profiler on a Dynamics Test Environment to see how Deployment Manager queries the Dynamics databases for to get this list.

When you right click in deployment manager and click Import Organization, it runs 3 queries under the user account you are connecting with.

1. It looks up all the CRM databases in the SQL instance.

SELECT name FROM sysdatabases WHERE name LIKE '%_MSCRM'

2. It checks to see if the database is a valid CRM database by checking for the presence of the OrganizationBase table in sys.objects.

SELECT * FROM sys.objects WHERE name='OrganizationBase' AND type='U'

3. It then checks to see if the database version is compatible with your current installation.

SELECT MajorVersion, MinorVersion, BuildNumber, Revision FROM BuildVersion

So, if you are having problems Importing Organizations, ask your DBA to add permissions to query sysdatabases, sys.objects and the BuildVersion table in the CRM database you are trying to import. In my case, my user account was able to query sysdatabases, but querying sys.objects and BuildVersion returned no results which caused the error above.

Once your DBA adds these permissions, you should be able to import the database successfully.