Menu

Client Portal Scripts Updated for ConnectWise Version 2016.3

Google+0
LinkedIn5

In the past, we shared some handy scripts that help you manage your ConnectWise client portal access using SQL directly. But with CW Version 2016.3, the schema changed. Here are the updated scripts that will reflect these changes.

Client Portal Scripts Updated to Reflect ConnectWise 2016.3 Schema Changes

Each script below has been updated and includes a link to the post in which it was originally shared. The assumptions from that post are still the same where the scripts restrict to a specific company type and status. The core difference is that now client portal access settings are stored in a new table (Contact_Portal_Access).

Set a default client portal password

FROM: https://www.nexnow.net/blog/2011/11/24/set-client-portal-passwords-using-sql/

USE [cwwebapp_site]
GO
UPDATE Contact_Portal_Access
SET    Contact_Portal_Access.CM_Password = (SELECT [dbo].[udf_EncrDecr] ('bananas!', 'E'))
FROM Contact_Portal_Access  
       INNER JOIN Contact
	 ON Contact.Contact_RecID = Contact_Portal_Access.Contact_RecID
       INNER JOIN Company
         ON Contact.Company_RecID = Company.Company_RecID
       INNER JOIN Company_Status
         ON Company.Company_Status_RecID = Company_Status.Company_Status_RecID
       INNER JOIN Company_Type
         ON Company.Company_Type_RecID = Company_Type.Company_Type_RecID
WHERE  ( Company_Status.Company_Status_RecID = 1 )
   AND ( Company_Type.Company_Type_RecID = 1 )
   AND ( Contact_Portal_Access.CM_Password IS NULL )
   AND ( Contact.Inactive_Flag = 0 )

Set portal security for primary contact

FROM: https://www.nexnow.net/blog/2012/01/24/managing-connectwise-client-portal-security-and-access-using-sql/

UPDATE Contact_Portal_Access
SET    Contact_Portal_Access.Portal_Security_Level = 6
FROM Contact_Portal_Access  
       INNER JOIN Contact
	 ON Contact.Contact_RecID = Contact_Portal_Access.Contact_RecID
       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_Access.Portal_Security_Level = 0 )

Set portal security level for billing contact

FROM: https://www.nexnow.net/blog/2012/01/24/managing-connectwise-client-portal-security-and-access-using-sql/

UPDATE Contact_Portal_Access
SET    Contact_Portal_Access.Portal_Security_Level = 4
FROM Contact_Portal_Access  
       INNER JOIN Contact
	 ON Contact.Contact_RecID = Contact_Portal_Access.Contact_RecID
       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_Access.Portal_Security_Level = 0 )

Revoke access to the client portal for inactive contacts

FROM: https://www.nexnow.net/blog/2012/01/24/managing-connectwise-client-portal-security-and-access-using-sql/

UPDATE Contact_Portal_Access
SET    Disable_Flag = 1
FROM   dbo.Contact_Portal_Access
	INNER JOIN Contact
		ON Contact.Contact_RecID = Contact_Portal_Access.Contact_RecID
WHERE  ( Contact.Inactive_Flag = 1 )

Revoke access to the client portal for inactive clients

FROM: https://www.nexnow.net/blog/2012/01/24/managing-connectwise-client-portal-security-and-access-using-sql/

UPDATE Contact_Portal_Access
SET  Contact_Portal_Access.Disable_Flag = 1
FROM Contact_Portal_Access  
       INNER JOIN Contact
	 ON Contact.Contact_RecID = Contact_Portal_Access.Contact_RecID
       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 ConnectWise; then update line 12 accordingly.

Are none of these the scripts you’re looking for? Check out all the other ways we can help you manage ConnectWise better.

Related Posts