SQL Security Roles information

Here is an SQL to retrieve all security roles privileges for all entities to help with comparing or to be used in an SSRS report

SELECT
FilteredRole.name as UserName,
EntityView.PhysicalName,
EntityView.OriginalLocalizedName as DisplayName,
Privilege.Name as PrivilegeName,
AccessLevel =
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,
PrivilegeType =
CASE PrivilegeDepthMask
WHEN 1 THEN ‘Own Only’
WHEN 2 THEN ‘Business Unit’
WHEN 4 THEN ‘Parent->Child’
WHEN 8 THEN ‘Organization’
WHEN 16 THEN ‘Own Only – Inherited’
WHEN 32 THEN ‘Business Unit – Inherited’
WHEN 64 THEN ‘Parent->Child – Inherited’
WHEN 128 THEN ‘Organization – Inherited’
END
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
WHERE FilteredRole.roletemplateid is null and OriginalLocalizedName is not null
ORDER BY FilteredRole.name, EntityView.PhysicalName, Privilege.AccessRight

Advertisements
Posted in Helpers, SQL | Tagged , , | Leave a comment

SQL get picklist details (CRM 4.0)

The following SQL can be used to retrieve information about picklists in CRM 4.0

SELECT  LocalizedLabelView_1.Label AS EntityDisplayName, EntityView.Name AS EntityName,
LocalizedLabelView_2.Label AS AttributeDisplayName, AttributeView.Name AS AttributeName ,
StringMap.AttributeValue, StringMap.Value AS AttributeValueCaption,
AttributeTypes.Description as Type,AttributeTypes.XmlType,AttributeView.Length,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity,AttributeView.AttributeRequiredLevelId
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN       AttributeView
ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN       EntityView
INNER JOIN       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON       AttributeView.EntityId = EntityView.EntityId
INNER JOIN attributetypes on AttributeView.AttributeTypeId = AttributeTypes.AttributeTypeId
INNER JOIN
AttributePicklistValueView ON AttributeView.AttributeId = AttributePicklistValueView.AttributeId
INNER JOIN
StringMap ON StringMap.AttributeValue = AttributePicklistValueView.Value
AND StringMap.AttributeName = AttributeView.Name
AND StringMap.ObjectTypeCode = EntityView.ObjectTypeCode
AND EntityView.EntityId = AttributeView.EntityId
WHERE   LocalizedLabelView_1.ObjectColumnName = ‘LocalizedName’ AND LocalizedLabelView_2.ObjectColumnName = ‘DisplayName’
AND LocalizedLabelView_1.LanguageId = ‘1033’ AND LocalizedLabelView_2.LanguageId = ‘1033’
ORDER BY EntityDisplayName, AttributeName

Posted in Helpers, SQL | Tagged , , , , , | Leave a comment

SQL get optionset details

The followin SQL can be used to retrieve information about optionsets in all entitesSELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName,
Stringmap.Value,Stringmap.AttributeValue,StringMap.DisplayOrder,
AttributeTypes.Description as Type,
AttributeTypes.XmlType,AttributeView.Length,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN       AttributeView
ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN       EntityView
INNER JOIN       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON       AttributeView.EntityId = EntityView.EntityId
INNER JOIN attributetypes on AttributeView.AttributeTypeId = AttributeTypes.AttributeTypeId
INNER JOIN StringMap on StringMap.ObjectTypeCode = EntityView.ObjectTypeCode and StringMap.AttributeName = AttributeView.Name
WHERE   LocalizedLabelView_1.ObjectColumnName = ‘LocalizedName’ AND LocalizedLabelView_2.ObjectColumnName = ‘DisplayName’
AND LocalizedLabelView_1.LanguageId = ‘1033’ AND LocalizedLabelView_2.LanguageId = ‘1033’
AND AttributeTypes.Description in(‘picklist’,’state’,’status’)
ORDER BY EntityDisplayName, AttributeName

Posted in Helpers, SQL | Tagged , , , , | Leave a comment

SQL get attributes details of entities

The following SQL can be used to retrieve information and details about all attributes in an entity.

SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName,AttributeTypes.Description as Type,
AttributeTypes.XmlType,AttributeView.Length,AttributeView.MaxLength,AttributeView.AttributeRequiredLevelId,Accuracy,IsCustomEntity,IsMappable,EntityView.IsCustomizable,EntityView.IsRenameable,IsActivity
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN       AttributeView
ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN       EntityView
INNER JOIN       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON       AttributeView.EntityId = EntityView.EntityId
INNER JOIN attributetypes on AttributeView.AttributeTypeId = AttributeTypes.AttributeTypeId
WHERE   LocalizedLabelView_1.ObjectColumnName = ‘LocalizedName’ AND LocalizedLabelView_2.ObjectColumnName = ‘DisplayName’
AND LocalizedLabelView_1.LanguageId = ‘1033’ AND LocalizedLabelView_2.LanguageId = ‘1033’
ORDER BY EntityDisplayName, AttributeName

Posted in Helpers, SQL | Leave a comment

Javascript call to a WCF method

Suppose we have a WCF method with URL ‘http://myserver/myservice.svc’ and a method MyMethod which expectes a parameter para1. We can call this WCF method using XMLHTTP, by using the following code:

var xmlHttp3 = new ActiveXObject(‘Microsoft.XMLHTTP’);
xmlHttp3.open(‘get’, ‘http://myserver/myservice.svc//MyMethod?para1=’ + value, false);
xmlHttp3.send();

Another method of call would be through jquery

function AjaxSend(url, data) {

/// <summary>

/// Sends request to WCF method<br/>The format of the url and data can be customised on the WCF side by specifying the attribute UriTemplate for the WebInvoke method decoration

/// </summary>

/// <param name=”url”>URL of WCF method<br/>

Example: http://MyServer/myservice.svc/MyMethod</param>

/// <param name=”data”>Parameters to pass to the WCF method<br/>

Example: para1=value</param>

/// <returns>Response object from the WCF method</returns>

var response;

$.ajax({

type: “GET”, //GET or POST or PUT or DELETE verb

url: url, // Location of the service

data: data, //Data sent to server

contentType: “application/json; charset=utf-8”, // content type sent to server

dataType: “json”, //Expected data format from server

processdata: true, //True or False

async: false,

success: function (msg) {//On Successfull service call

response = msg;

},

error: function (result) {// When Service call fails

alert(‘Service call failed: ‘ + result.status + ‘ – ‘ + result.statusText);

if (result.responseText) {

var err = result.responseText;

if (err)

error(err);

else

error({ Message: “Unknown server error.” })                 }

return;

}

});

return response;

}

Posted in Helpers, Javascript | Tagged , | Leave a comment

Javascript Common issues with CRM

Important random notes you need to be cautious about when developing client side script for CRM forms.

  1. Always make sure the URL of the page is not localhost (Should be the server name). If you have a script code into which there is a reference to the current address of the page, then the use of ‘localhost’ will not work and will cause unexpected behaviour from your code.
  2. If a subgrid is not showing on a form. Or you are getting the annoying microsoft error message with “Do not send” or “Send” message every now and then. Always make sure the browser settings are set to support XMLHTTP.
    • Internet Options->Advanced->Under Security check Enable Native XMLHTTP Support.
  3. If you are ever using JQuery for cross site calls, and you are getting error “Service call failed: 0 – No Transport”, then make sure you are using jquery1.4 and not a higher version. However, this might not be a correct advice as more jquery versions are released since this note was taken.
Posted in Error, Javascript, Uncategorized | Tagged , , , , | Leave a comment

Sharepoint Error: Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 800703fa.

I have googled a lot about this and couldn’t find a specific clear explanation for the problem. But found a practical solution which is IISRESET or maybe restart the sharepoint server.

Posted in Error, Sharepoint, Uncategorized | Tagged , , | Leave a comment