Here is a quick SQL statement to expose all security permissions within MAS500 (both task and events) by user group. The security structure in the tables in MAS500 is misleading, because both the tsmSecurEventPerm and tsmTaskPerm tables do not contain a record for every user group unless a change is made. This view accounts for that and returns the full set.
IF object_id(N'dbo.vsmSecurPerms_RKL', 'V') IS NOT NULL
DROP VIEW dbo.vsmSecurPerms_RKL
GO
CREATE VIEW dbo.vsmSecurPerms_RKL
AS
select md.ModuleID
, ug.UserGroupID
, case ISNULL(sem.Authorized,0)
when 0 then 'No'
when 1 then 'Yes'
end as 'Permission'
, ls.LocalText as Description
, 'Event' as SecType
from tsmusergroup ug with (nolock) cross join
tsmSecurEvent se with (nolock) inner join
tsmModuleDef md with (nolock)
on md.ModuleNo = se.ModuleNo
left outer join tsmSecurEventPerm sem with (nolock)
on se.SecurEventID = sem.SecurEventID and sem.UserGroupID = ug.UserGroupID
inner join tsmLocalString ls with (nolock)
on se.DescStrNo = ls.StringNo
UNION ALL
select md.ModuleID
, ug.UserGroupID
, case tp.Rights
when 1 then 'Excluded'
when 2 then 'Display Only'
when 3 then 'Normal'
when 4 then 'Supervisory'
else 'Excluded'
end 'Rights'
, tsd.TaskLongName
, 'Task' As SecType
from tsmUserGroup ug with (nolock)
cross join tsmTask t with (nolock)
left outer join tsmTaskPerm tp with (nolock)
on ug.UserGroupID = tp.UserGroupID and tp.TaskID = t.TaskID
join tsmModuleDef md with (nolock)
on t.ModuleNo = md.ModuleNo
join tsmTaskTypeDef ttd with (nolock)
on t.TaskTypeID = ttd.TaskTypeID
join tsmModuleStrDef msd with (nolock)
on t.ModuleNo = msd.ModuleNo
join tsmTaskStrDef tsd with (nolock)
on t.TaskID = tsd.TaskID
GO
GRANT SELECT ON dbo.vsmSecurPerms_RKL TO PUBLIC
GO
GRANT SELECT ON dbo.vsmSecurPerms_RKL TO ApplicationDBRole
GO
select * from vsmSecurPerms_RKL
Joe Noll
RKL eSolutions LLC
http://www.rklesolutions.com
