Audit Parsing

declare @dt datetime
select @dt = getdate()-4
--select @dt


 IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
 IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
 IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;


 CREATE TABLE #audit(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
attributemask nvarchar(max),
changedata nvarchar(max),
operation int,
changedOn datetime,
changedBy nvarchar(200)
)

 CREATE TABLE #attribute(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
atribute int,
operation int,
changedOn datetime,
changedBy nvarchar(200)

)

 CREATE TABLE #value(
[id] [bigint] IDENTITY(1,1) NOT NULL,
objectid uniqueidentifier,
objectTypeCode int,
value nvarchar(max)
)



INSERT INTO #audit(objectid,attributemask,changedata, objectTypeCode, operation, changedOn, changedBy)

select


objectid ,substring(attributemask,2,len(attributemask)-2) AttributeMask,ChangeData, objectTypeCode, Operation, CreatedOn, UserIdName


from Audit a


WHERE
a.operation in  (1,2)
AND a.Action in  (1,2)

AND a.ObjectTypeCode = 2

and a.CreatedOn >= @dt
--and a.useridname = 'sa_CRM_Delta, sa_CRM_Delta'
AND a.ChangeData!=''




declare @rowNum bigint
DECLARE @objectid uniqueidentifier
declare @attributemask nvarchar(max)
declare @changedata nvarchar(max)
DECLARE @auxAtrib xml
DECLARE @auxVals xml
declare @objectTypeCode int

declare @operation int
declare @changedOn datetime
declare @changedBy nvarchar(200)


declare @splitAttrib char(1)
set @splitAttrib = ','

declare @splitVals char(1)
set @splitVals = '~'


DECLARE audit_cursor CURSOR FOR
SELECT id,objectid,attributemask,changedata, objectTypeCode,operation, changedOn, changedBy
FROM #audit

open audit_cursor

FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy


WHILE @@FETCH_STATUS = 0
BEGIN


SELECT @auxAtrib = CONVERT(xml,' <root> <s>' + REPLACE(@attributemask,@splitAttrib,'</s> <s>') + '</s>   </root> ')
SELECT @auxVals = CONVERT(xml,' <root> <s>' + REPLACE(   replace(@changedata,'&','&amp;')     ,@splitVals,'</s> <s>') + '</s>   </root> ')

INSERT INTO #attribute(objectid, objectTypeCode , atribute,operation,changedOn,changedBy)
SELECT @objectid id, @objectTypeCode, atrib = T.c.value('.','varchar(20)') , @operation, @changedOn, @changedBy
FROM @auxAtrib.nodes('/root/s') T(c)

INSERT INTO #value(objectid, objectTypeCode, value)
SELECT @objectid id, @objectTypeCode, val = T.c.value('.','varchar(20)')
FROM @auxVals.nodes('/root/s') T(c)









FETCH NEXT FROM audit_cursor
INTO @rowNum,@objectid,@attributemask,@changedata, @objectTypeCode,@operation,@changedOn,@changedBy

END

CLOSE audit_cursor;
DEALLOCATE audit_cursor;



--truncate table StagingDB.dbo.Audit

--INSERT INTO StagingDB.dbo.Audit


SELECT

--v.objectid

--,v.value

--,a.atribute

 CASE a.operation
WHEN 1 then 'Create'
WHEN 2 then 'Update'
 END  operation
,a.changedOn
,cast(a.changedOn as date) changedOn_date
,a.changedBy
,at.Name
,c.hb_ConsumerId
,c.Address1_Country







FROM  #value v
INNER JOIN #attribute a
ON v.id = a.id

INNER JOIN Row2015_MSCRM.dbo.Contact c
ON v.objectid = c.ContactId and c.statecode=0
INNER JOIN (
SELECT DISTINCT ar.name,     ar.ColumnNumber
FROM MetadataSchema.Attribute ar INNER JOIN
MetadataSchema.Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 ) at
ON a.atribute = at.ColumnNumber






 IF OBJECT_ID('tempdb..#audit') IS NOT NULL DROP TABLE #audit;
 IF OBJECT_ID('tempdb..#attribute') IS NOT NULL DROP TABLE #attribute;
 IF OBJECT_ID('tempdb..#value') IS NOT NULL DROP TABLE #value;

Script to delete CRM Users in CRM 2015

USE YourCrm_MSCRM
BEGIN TRANSACTION
DECLARE @username AS VARCHAR(50)

-- CHANGE THIS --
SET @username = 'yourDomain\yourUser'

DECLARE @userId AS UNIQUEIDENTIFIER
SET @userId = (SELECT SystemUserId  FROM dbo.SystemUserBase WHERE DomainName = @username)

DECLARE @orgid AS UNIQUEIDENTIFIER
SET @orgid = (SELECT OrganizationId FROM dbo.SystemUserBase WHERE systemuserid = @userid)

DECLARE @userEmail AS VARCHAR(MAX)
SET @useremail = (SELECT InternalEMailAddress FROM dbo.SystemUserBase WHERE SystemUserId = @userid)

DECLARE @userfullname AS VARCHAR(max)
SET @userfullname = (SELECT fullname FROM dbo.systemuserbase WHERE systemuserid = @userid)

DECLARE @queueid AS UNIQUEIDENTIFIER
SET @queueid = (SELECT queueid FROM dbo.SystemUserBase WHERE SystemUserId = @userid)

DECLARE @ownerid AS UNIQUEIDENTIFIER
SET @ownerid = (SELECT ownerid FROM dbo.OwnerBase WHERE name = @userfullname)

DELETE FROM dbo.UserSettingsBase WHERE SystemUserId = @userId
DELETE FROM dbo.TeamMembership WHERE SystemUserId = @userId
DELETE FROM dbo.SystemUserPrincipals WHERE systemuserid = @userId
DELETE FROM dbo.SystemUserRoles WHERE systemuserid = @userId

DELETE FROM dbo.QueueMembership where systemuserid =  @userId
DELETE FROM dbo.MailboxBase where ownerid =  @userid

DELETE FROM dbo.SystemUserBusinessUnitEntityMap WHERE systemuserid = @userid
DELETE FROM dbo.SystemUserBase WHERE SystemUserId = @userid
DELETE FROM dbo.QueueBase WHERE QueueId = @queueid
DELETE FROM dbo.PrincipalEntityMap WHERE PrincipalId = @ownerid
DELETE FROM dbo.PrincipalObjectAccess WHERE principalid = @ownerid
DELETE FROM dbo.OwnerBase WHERE ownerid = @ownerid
DELETE FROM dbo.EmailSearchBase WHERE EmailAddress = @userEmail
DELETE FROM dbo.ResourceBase WHERE name = @userfullname
DELETE FROM dbo.CalendarRuleBase WHERE CalendarId IN (SELECT CalendarId FROM dbo.CalendarBase WHERE PrimaryUserId = @userid)
DELETE FROM dbo.CalendarBase WHERE primaryuserid = @userId
DELETE FROM dbo.InternalAddressBase WHERE parentid = @userId

DELETE FROM mscrm_config..SystemUserOrganizations WHERE CrmUserId = @userid AND OrganizationId = @orgid

COMMIT

"Database cannot be started in this edition of SQL Server" error when restoring a Microsoft Dynamics CRM database

https://support.microsoft.com/en-us/kb/2567984

Enable Tracing for CRM 2015 - OnPremise

https://support.microsoft.com/en-us/kb/907490

How to use Dynamics CRM Project Templates in VS 2013

https://community.dynamics.com/crm/b/tsgrdcrmblog/archive/2014/08/23/microsoft-dynamics-crm-2013-toolkit-with-visual-studio-2013

Dynamics CRM 2015 Training Videos

Dynamics CRM 2015 Videos

The SELECT permission was denied on the object 'EntityView', database 'Fashion_MSCRM', schema 'dbo'.

Sometimes, when you create or import a new CRM Organization, the new CRM Organization does not work..

If you take a look in Event Viewer, you will see a message like : The SELECT permission was denied on the object 'EntityView', database 'Fashion_MSCRM', schema 'dbo'.

For this, you can give explicit permissions on that object :

GRANT SELECT ON dbo.EntityView
TO [CRMReaderRole]
GO


Or, if you give that permission, and its still requires more permissions, for other objects, you can do it like :

EXEC sp_addrolemember N'db_owner', N'CRMReaderRole'
GO