User Group Clean up

// Please put sql in here
-- unassign user permissions from security groups

select * from tbGroup where GroupName in ('BA','Head Office Admin','SC Operator','Store Default','CM Operator','Labor Management','Labor - View All Wages','Labor - Maintain Break Policies','Employee Manager','Data Export')


Begin Tran
update tbGroup
set 
Usage1=0,Usage2=0,Usage3=0,Usage4=0,Usage5=0,Usage6=0,Usage7=0,Usage8=0,Usage9=0,usage10=0,Usage11=0,usage12=0,MobileUsage1=0,MobileUsage2=0,MobileUsage3=0
where groupname in ('BA','Head Office Admin','SC Operator','Store Default','CM Operator','Labor Management','Labor - View All Wages','Labor - Maintain Break Policies','Employee Manager','Data Export')
commit Tran
Rollback tran

------------------------------------

--List of users and the security groups they belong to. Those are the 10 security groups with Zero users in production, cleaned JAT,Testing and stating to look the same

select tbu.userId,tbu.UserName as username, tbu.LogonDisabled, tbu.Status, groupname, tbgu.groupid
from tbuser tbu with (nolock), tbgroup with (nolock) left outer join tbgroupuser tbgu on tbgroup.groupid = tbgu.groupid
where tbgu.userid = tbu.UserID and Status= 'Active' and tbgu.groupID in (14,27,28,29,41,52,54,55,59,73) -- can change the Status to 'Terminated' to see  inactive users.
order by username, GroupName

-- unassign users from securityGroups 
select * from tbgroupuser where groupID in (14,27,28,29,41,52,54,55,59,73) -- Those are the 10 security groups with Zero users in production, cleaned in Testing, staging  JAT and production.

Begin Tran
delete from tbgroupuser 
where groupID in (14,27,28,29,41,52,54,55,59,73)
--Commit Tran
--Rollback