Back to Home

CEM Revoke User

Overview

Business will make a quarterly request to remove user roles and inactivate users for CEM and/or QUAD.

Script 1: Revoke User

  1. Copy USER_ACCESS (Column D) and EMP_EEID (Column E) to new excel file

    • See template file: Excel Template
    • Paste into new query window in SQL
    • Highlight extra white space and do a Ctrl + H and replace all excel tabbed spaces with just an empty value.
      • Capture.png

  2. Add App and UserNo columns to #Users temp table

    • the update script will replace the UserRole string with int value
    • App value will b used to indicate which application's UserRoles needs to be updated
    • UserNo will be updated by the join with sam_SetupUser table on EmployeeID
      • NOTE: Could have added to the #Users temp table in step 1, but this seems quicker as you don't have to add extra values to the excel file.

  3. Delete user roles for appropriate UserNo and application type they should not have.

  4. Get UserNo that still have the role.
  5. Inactivate users that no longer have the specified role.
USE CORE_CEM
GO
SET QUOTED_IDENTIFIER ON
GO

-- 1
CREATE TABLE #Users (UserRole VARCHAR(60), EmployeeID VARCHAR(8))
INSERT INTO #Users SELECT '[CEM]:Advocate-CEM : .','121212'
INSERT INTO #Users SELECT '[CEM]:BackUp Quality Manager-QUAD : .','256789'
INSERT INTO #Users SELECT '[CEM]:Gate Keeper-QUAD : .','863321'
INSERT INTO #Users SELECT '[CEM]:Advocate-CEM : .','458382'
INSERT INTO #Users SELECT '[CEM]:Business Owner-QUAD : .','111234'

-- 2
ALTER TABLE #Users ADD App VARCHAR(4)
ALTER TABLE #Users ADD UserNo INT

UPDATE #Users SET UserRole = 2,  App = 'CEM' WHERE UserRole = '[CEM]:Advocate-CEM : .'
UPDATE #Users SET UserRole = 3,  App = 'QUAD' WHERE UserRole = '[CEM]:Gate Keeper-QUAD : .'
UPDATE #Users SET UserRole = 6,  App = 'QUAD' WHERE UserRole = '[CEM]:BackUp Quality Manager-QUAD : .'
UPDATE #Users SET UserRole = 9,  App = 'QUAD' WHERE UserRole = '[CEM]:Business Owner-QUAD : .'

UPDATE a SET UserNo = b.UserNo FROM #Users a
INNER JOIN smart.sam_SetupUser b on a.EmployeeID = b.EmployeeID

-- 3
UPDATE a SET Deleted = 1 FROM smart.sam_User_JobType a WHERE EXISTS (
SELECT EmployeeID FROM #Users WHERE UserRole = a.JobTypeNo AND UserNo = a.UserNo AND App = 'CEM')

UPDATE a SET Deleted = 1 FROM smart.p11_User_JobType a WHERE EXISTS (
SELECT EmployeeID FROM #Users WHERE UserRole = a.JobTypeNo AND UserNo = a.UserNo AND App = 'QUAD')

-- 4
SELECT DISTINCT UserNo INTO #UsersHavingRole FROM smart.sam_User_JobType WHERE UserNo IN (SELECT UserNo FROM #Users) AND Deleted = 0
UNION
SELECT DISTINCT UserNo FROM smart.p11_User_JobType WHERE UserNo IN (SELECT UserNo FROM #Users) AND Deleted = 0

-- 5
UPDATE smart.sam_SetupUser SET CurrentStatus = 0, LoginAllowed = 0
WHERE UserNo IN (SELECT UserNo FROM #Users) AND UserNo NOT IN (SELECT UserNo FROM #UsersHavingRole)