CRM 4 UserQuery Privileges for System Administrators
By Carlton Colter - Last updated: Wednesday, February 4, 2009 - Save & Share - Leave a Comment
One of the problems I noticed in a recent upgrade to CRM 4 was that the System Administrators group did not have access to assign other people’s saved advanced finds (user queries). This is extremely annoying because I have a single aspx page that uses the CRM SDK to allow a system administrator to move any saved view from being owned by one person to another.
Saved views are great for the organization, departments, and teams, but when a team uses them, often times they have someone create and share the views, then they leave the company or move to a different group within the company. They either can’t or no longer wish to be responsible for the advanced finds they have created and shared out with their fellow CRM users.
My custom page allows me to assign those views to someone else, allowing them to move the responisibility to another CRM user.
To update the CRM privileges to allow System Administrators full access (except for read) to all views, you can run the following sql code against the database.
SQL Code:
UPDATE RolePrivileges SET PrivilegeDepthMask=128 WHERE RolePrivilegeId IN
(
SELECT RolePrivilegeId FROM RolePrivileges r
INNER JOIN PrivilegeBase pb ON r.privilegeid=pb.privilegeid
INNER JOIN RoleBase rb ON r.roleid = rb.roleid
WHERE pb.Name in
('prvAssignUserQuery',
'prvShareUserQuery',
'prvWriteUserQuery',
'prvDeleteUserQuery',
'prvCreateUserQuery'
)
AND rb.Name = 'System Administrator'
)
The code for reassigning objects is really straight forward, it uses sql to get the lists, then uses the sdk to assign the items to other users. It doesn’t fail because of the sql modification above.
ASP.Net Code
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="reassign.aspx.cs" Inherits="mst_Support.userquery.reassign" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<link href="../css/support.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div align="center">
<asp:ScriptManager ID="smgr" runat="server">
</asp:ScriptManager>
<br />
<b>Source User:</b>
<asp:DropDownList ID="User_Src" runat="server" DataSourceID="crm_SystemUsers" DataTextField="FullName"
DataValueField="SystemUserId" AutoPostBack="True" OnSelectedIndexChanged="User_Src_SelectedIndexChanged">
</asp:DropDownList>
<br />
<asp:UpdatePanel ID="upnQueries" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<br />
<b>Saved Advanced Finds:<br />
</b>
<asp:ListBox ID="QueueList" runat="server" SelectionMode="Multiple" Rows="15"></asp:ListBox>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="User_Src" EventName="SelectedIndexChanged" />
</Triggers>
</asp:UpdatePanel>
<br />
<b>Target User:</b>
<asp:DropDownList ID="User_Target" runat="server"
DataSourceID="crm_SystemUsers" DataTextField="FullName"
DataValueField="SystemUserId">
</asp:DropDownList>
<br />
<br />
<asp:Button ID="Assign" runat="server" onclick="Assign_Click" Text="Assign" />
<br />
<br />
<asp:Label ID="lblResponse" runat="server"></asp:Label>
<br />
<br />
<asp:SqlDataSource ID="crm_SystemUsers" runat="server" ConnectionString="<%$ ConnectionStrings:crm_db %>"
SelectCommand="SELECT [SystemUserId], [FullName] FROM [SystemUserBase] WHERE ([IsDisabled] = @IsDisabled) ORDER BY [FullName]"
ProviderName="<%$ ConnectionStrings:crm_db.ProviderName %>">
<SelectParameters>
<asp:Parameter DefaultValue="false" Name="IsDisabled" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
C-Sharp code
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using mst_Support.crmsdk;
using System.Web.Services.Protocols;
using System.Web.Configuration;
namespace mst_Support.userquery
{
public partial class reassign : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public void AssignUserQuery(string queryid, string userid)
{
try
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; //AD
token.OrganizationName = "Your Organization"; //Could retrieve it.
CrmService crmService = new CrmService();
crmService.CrmAuthenticationTokenValue = token;
crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;
//crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;
SecurityPrincipal newowner = new SecurityPrincipal();
newowner.Type = SecurityPrincipalType.User;
newowner.PrincipalId = new Guid(userid);
TargetOwnedUserQuery query = new TargetOwnedUserQuery();
query.EntityId = new Guid(queryid);
// Create the request object.
AssignRequest assign = new AssignRequest();
// Set the properties of the request object.
assign.Assignee = newowner;
assign.Target = query;
AssignResponse assignResponse = (AssignResponse)crmService.Execute(assign);
}
catch (SoapException ex)
{
lblResponse.Text += "<b>Error: </b>" +ex.ToString() +
"<br /><br />\r\n";
lblResponse.Text += "<b>Inner Detail: </b>" + ex.Detail.InnerText +
"<br /><br />\r\n";
lblResponse.Text += "<b>InnerException: </b>" + ex.InnerException.ToString() +
"<br /><br />\r\n";
lblResponse.Text += "<b>StackTrace: </b>" + ex.StackTrace.ToString() +
"<br /><br />\r\n";
}
}
protected void User_Src_SelectedIndexChanged(object sender, EventArgs e)
{
string query = "SELECT [Name], [UserQueryId] FROM [UserQueryBase] WHERE ([OwningUser] = '"+
User_Src.SelectedValue+"') ORDER BY [Name]";
// Pull the crm_db connection string for the webconfig. (you could just replace this with
// your connection string.
SqlDataAdapter da = new SqlDataAdapter(query,
WebConfigurationManager.ConnectionStrings["crm_db"].ToString());
DataSet ds = new DataSet();
da.Fill(ds);
QueueList.DataSource = ds.Tables[0].DefaultView;
QueueList.DataTextField = "Name";
QueueList.DataValueField = "UserQueryId";
QueueList.DataBind();
upnQueries.Update();
}
protected void Assign_Click(object sender, EventArgs e)
{
try
{
foreach (ListItem li in QueueList.Items)
{
if (li.Selected)
{
lblResponse.Text += "Item: " + li.Value + " to: ";
lblResponse.Text += User_Target.SelectedValue + "<br />";
AssignUserQuery(li.Value, User_Target.SelectedValue);
}
}
lblResponse.Text += "<br />Moved Successfully";
User_Src_SelectedIndexChanged(sender, e);
}
catch (Exception ex)
{
lblResponse.Text += ex.ToString() + "< br/>";
lblResponse.Text += "Please report this error to internal systems.<br />";
}
}
}
}