For version 10/MSSQL 2014 and later version:
This will list the users and the usergroup it belongs to and the user privilege (in binary).
select oh_u.OH_Client, oh_u.oh_name USR, oh_g.oh_name USR_GRP, USR_EMail1, USR_EMail2, USR_LastSession , USR_Privilege
from
OH oh_u
,USR left outer join USRG
on usrg_usr_idnr = usr_oh_idnr
left outer join OH oh_g
on usrg_usg_idnr = oh_g.OH_Idnr
where 1=1
and oh_u.OH_DeleteFlag = 0
and oh_u.oh_idnr = usr_oh_idnr
and oh_u.oh_client > 0
This one should show you all the privilege that a user does *NOT* have (using the custom functions f_getPriv and f_getPrivName):
select oh_u.OH_Client, oh_u.oh_name USR, oh_g.oh_name USR_GRP, USR_EMail1, USR_EMail2, USR_LastSession , USR_Privilege
, case when USR_Privilege > 0
then convert(varchar(max),dbo.f_getPriv(USR_Privilege))
else '0'
end as MISSING_PRIV
from
OH oh_u
,USR left outer join USRG
on usrg_usr_idnr = usr_oh_idnr
left outer join OH oh_g
on usrg_usg_idnr = oh_g.OH_Idnr
where 1=1
and oh_u.OH_DeleteFlag = 0
and oh_u.oh_idnr = usr_oh_idnr
and oh_u.oh_client > 0
and usr_privilege > 0
RESULT:
https://us.v-cdn.net/5019921/uploads/editor/bz/igvs0ubira8o.png" width="1147">
FUNCTIONS:
create function [dbo].[f_getPrivName]
(@p_ID as varchar(100), @privTable privTableType READONLY)
RETURNS varchar(100)
as
begin
declare @privText as varchar(100);
select @privText = name from @privTable
where 1=1
and priv_ID = @p_ID;
return @privText;
end
-- Note that privileges 8,9, 11, and 17 are not used.
CREATE function [dbo].[f_getPriv] (@p_privBinary as bigint)
returns varchar(max)
as
begin
declare @totalPriv bigint
, @priv bigint
, @privDeny bigint
, @privBinary varchar(100)
, @privDenyBinary varchar(max)
, @n bigint
, @mod int
, @ctr int
, @privDenyText varchar(max);
declare @listOfIDs table(IDs varchar(100));
declare @listOfPriv as dbo.privTableType;
insert into @listOfPriv
values ('0','change system status')
, ('1','access to system overview')
, ('2','access to recycle bin')
, ('3','access to transport case')
, ('4','view messages from own user group')
, ('5','view messages to administrators')
, ('6','view all messages from own client')
, ('7','view security messages')
, ('8',' 0')
, ('9',' 0')
, ('10','access to selective statistic')
, ('11',' 0')
, ('12','create and modify backend variables')
, ('13','deal with authorizations at object level')
, ('14','modify the status of a task manually')
, ('15','object properties: allow manual reset of edit hint')
, ('16','file tranasfer: start without login object specified')
, ('17',' 0')
, ('18','view server usage of all clients')
, ('19','access <no folder>')
, ('20','logon via callAPI')
, ('21','SAP criteria manager')
, ('22','access to version management folder')
, ('23','access to autoforecast')
, ('24','create diagnostic information')
, ('25','take over taks')
, ('26','ilm actions')
, ('27','create and modify SQL internal variables')
, ('28','work in runbook mode')
, ('29','manage favorites in usergroup level')
, ('30','ecc: access to service catalog')
, ('31','ecc: access to process monitoring')
, ('32','ecc: access to service orchestrator')
, ('33','ecc: manage slas and business units in service orchestrator')
, ('34','ecc: access to policy orchestrator')
, ('35','ecc: access to process assembly')
, ('36','ecc: access to administration')
, ('37','ecc: access to dashboards')
, ('38','ecc: access to predictive analytics');
set @totalPriv = 549755679999;
set @priv = @p_privBinary; ---<------ PARAM
set @privDeny = @totalPriv - @priv;
set @n = @privDeny;
set @privDenyBinary = '';
set @privDenyText = '';
set @ctr = 0;
while (@n>0)
begin
set @mod = @n % 2;
if (@mod = 1)
begin
insert into @listOfIDs values(@ctr);
set @privDenyText = dbo.f_getPrivName(@ctr,@listOfPriv) + ', ' + @privDenyText ;
end
set @ctr = @ctr + 1;
set @privDenyBinary = convert(varchar(1),@mod) + @privDenyBinary;
set @n = @n/2;
end
return @privDenyText;
end