In Dynamics CRM 2011, after migrating an organization from Dynamics CRM Online to Dynamics CRM On Premise or Partner Hosted, you recieve a generic SQL Error when attempting to create a new entity.
In researching this, we found two issues:
The first issue surfaced in the following error message in the CRM Trace Logs:
CRM Trace Log Error:
System.Data.SqlClient.SqlException
(0x80131904): The EXECUTE permission was denied on the object ‘sp_MS_marksystemobject’,
database ‘mssqlsystemresource’, schema ‘sys’.
You can resolve this issue by running a grant execute script on sp_MS_marksystemobject on the SQLAccessGroup login in the Master Database. Now, don’t go looking for this stored procedure in the master database. You will not find it. It is actually in a hidden system database called mssqlsystemresource. However, running the grant statement in the Master database still works.
Completing this step may solve your problem. So, you should try adding an entity after this step is complete. If it still does not work, you may see the following error in the CRM Traced Log:
CRM Trace Log Error:
System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure ‘p_FreeProcCache’
If this is the issue, it means that the p_FreeProcCache procedure is missing entirely from your CRM Database. Run the following two scripts to (1) create the procedure in your CRM Organization database (Organization_MSCRM) and to (2) convert it to a system object. Once this is done, you should be able to create entities in CRM.
Statement 1:
Create proc [dbo].[p_FreeProcCache] AS
begin declare @i intselect @i =db_id();
DBCC FLUSHPROCINDB(@i);
end
Exec sys.sp_MS_marksystemobject ‘p_FreeProcCache’
Statement 2:
Exec sys.sp_MS_marksystemobject ‘p_FreeProcCache’