Menu

Managing ConnectWise client portal security and access using SQL

Google+0
LinkedIn0

Update Notice: If you are using ConnectWise version 2016.3 or later, the schema has changed. Check out this updated post for the changes.

As an follow up to the article I wrote in November about setting ConnectWise client portal passwords using SQL, here are several more ConnectWise client portal security 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:

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 )
       AND ( dbo.Contact.Portal_Security_Level = 0 )

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 – 14 makes sure you’re only targeting the primary contact attached to active clients who haven’t already been assigned a portal security level.

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.

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.Contact.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 )

Revoke access to the client portal for inactive contacts

This script disables access to the client portal for all contacts that have been marked as inactive within ConnectWise, regardless of the which type of company (client, vendor, etc.) they’re attached to or the status of that company.

UPDATE Contact
SET    Disable_Flag = 1
FROM   dbo.Contact
WHERE  ( Inactive_Flag = 1 )

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.

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.

As you can see, there are a number of manual tasks that you can automated using SQL. What SQL scripts is your company developed to manage ConnectWise?

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts