Managing ConnectWise client portal security and access using SQL

Hat tip to Brad Benner of Nexnow for this topic. Check out his awesome blog at http://www.nexnow.com/blog

As an follow up to the article about setting ConnectWise client portal passwords using SQL, here are several more SQL scripts that automate some of the tasks involved with on and off-boarding clients.

Please keep in mind that these scripts are intended to illustrate how somebody with a solid understanding of SQL and the ConnectWise database schema might automate what is currently a manual task in ConnectWise. It is possible that these scripts, as presented here, may not work in your environment, and should therefore be tested.

Set portal security for primary contact

This script sets the client portal security level for the primary contact for active clients in ConnectWise:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
UPDATE Contact
SET Contact.Portal_Security_Level = 6
FROM dbo.Contact
 INNER JOIN dbo.Company
 ON dbo.Contact.Company_RecID = dbo.Company.Company_RecID
 INNER JOIN dbo.Company_Status
 ON dbo.Company.Company_Status_RecID =
 dbo.Company_Status.Company_Status_RecID
 INNER JOIN dbo.Company_Type
 ON dbo.Company.Company_Type_RecID = dbo.Company_Type.Company_Type_RecID
WHERE ( dbo.Contact.Default_Flag = 1 )
 AND ( dbo.Company_Type.Company_Type_RecID = 1 )
 AND ( dbo.Company_Status.Company_Status_RecID = 1 )

In this example, we're setting the portal security level to the highest possible (6). Be sure to change Contact.Portal_Security_Level in line 1 to the level you typically assign to the primary contact for your clients (1-6, as defined in Setup Tables > Portal Security). The conditions in lines 11 - 13 makes sure you're only targeting the primary contact attached to active clients.

Set portal security level for billing contact

Similiar to the last example, except this script updates the portal security level for the client's billing contact.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
UPDATE Contact
SET Contact.Portal_Security_Level = 4
FROM dbo.Contact
 INNER JOIN dbo.Company
 ON dbo.Contact.Company_RecID = dbo.Company.Company_RecID
 INNER JOIN dbo.Company_Status
 ON dbo.Company.Company_Status_RecID =
 dbo.Company_Status.Company_Status_RecID
 INNER JOIN dbo.Company_Type
 ON dbo.Company.Company_Type_RecID = dbo.Company_Type.Company_Type_RecID
WHERE ( dbo.Company.Default_Billing_Flag = 1 )
 AND ( dbo.Company_Type.Company_Type_RecID = 1 )
 AND ( dbo.Company_Status.Company_Status_RecID = 1 )
 AND ( dbo.Contact.Portal_Security_Level = 0 )

Set portal security for all contacts as a specific company

This script sets the client portal security level for all contacts at a specific company in ConnectWise:

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE Contact
SET Contact.Portal_Security_Level = 1
FROM dbo.Contact
 INNER JOIN dbo.Company
 ON dbo.Contact.Company_RecID = dbo.Company.Company_RecID
 INNER JOIN dbo.Company_Status
 ON dbo.Company.Company_Status_RecID =
 dbo.Company_Status.Company_Status_RecID
 INNER JOIN dbo.Company_Type
 ON dbo.Company.Company_Type_RecID = dbo.Company_Type.Company_Type_RecID
WHERE ( dbo.Company.Company_ID = 'deskdirector' )

In this example, we're setting the portal security level to the lowest possible (1). Be sure to change Contact.Portal_Security_Level in line 1 to the level you want to assign to the contacts (1-6, as defined in Setup Tables > Portal Security). The condition in line 11 is where you set the CompanyID of the company whose contacts you want to change, change 'deskdirector' to the CompanyID required.

Revoke access to the client portal for inactive clients

This script disables access to the client portal for all contacts attached to an inactive client.

1
2
3
4
5
6
7
8
9
10
11
12
 
UPDATE Contact
SET Contact.Disable_Flag = 1
FROM dbo.Contact
 INNER JOIN dbo.Company
 ON dbo.Contact.Company_RecID = dbo.Company.Company_RecID
 INNER JOIN dbo.Company_Status
 ON dbo.Company.Company_Status_RecID =
 dbo.Company_Status.Company_Status_RecID
 INNER JOIN dbo.Company_Type
 ON dbo.Company.Company_Type_RecID = dbo.Company_Type.Company_Type_RecID
WHERE ( dbo.Company_Type.Company_Type_RecID = 1 )
 AND ( dbo.Company_Status.Company_Status_RecID = 3 )

Be sure to check the Company_Status table to verify which Company_Status_RecIDs represent an inactive company in your instance of ConnecWise; then update line 12 accordingly.

 

How did we do?

Contact