MDS Checking Users Security Settings

Hi,

Here are some sql scripts that you can run to check the security settings of a user in MDS (Master Data Services).

-------------------------------------------------------------------------------      
-- Model User Security
-------------------------------------------------------------------------------
SELECT * FROM mdm.udfSecurityUserModelList(5) ACL
INNER JOIN mdm.viw_SYSTEM_SCHEMA_MODEL vModel ON ACL.ID = vModel.ID
INNER JOIN mdm.tblSecurityPrivilege tPriv ON tPriv.ID = acl.Privilege_ID
-------------------------------------------------------------------------------      
-- Entity User Security
-------------------------------------------------------------------------------
SELECT vEnt.Model_ID,vEnt.Model_Name,
acl.ID AS 'Entity_ID', vEnt.Name AS 'Entity_Name', tPriv.Code
FROM    mdm.udfSecurityUserEntityList(5, NULL) acl
INNER JOIN mdm.viw_SYSTEM_SCHEMA_ENTITY vEnt ON  acl.ID = vEnt.ID 
INNER JOIN mdm.tblSecurityPrivilege tPriv ON tPriv.ID = acl.Privilege_ID
ORDER BY vEnt.Model_ID, vEnt.Name
-------------------------------------------------------------------------------      
-- Attribute Group User Security
-------------------------------------------------------------------------------
SELECT * 
FROM    mdm.udfSecurityUserAttributeGroupList(5, 4,149,1) acl
INNER JOIN mdm.viw_SYSTEM_SCHEMA_ATTRIBUTEGROUPS vAG ON  acl.ID = vAG.ID 
INNER JOIN mdm.tblSecurityPrivilege tPriv ON tPriv.ID = acl.Privilege_ID
ORDER BY vAG.Model_ID, vAG.Name
-------------------------------------------------------------------------------      
-- Attribute Group Attribute List User Security
-------------------------------------------------------------------------------
SELECT acl.USER_ID, acl.Model_ID, vAG.Model_Name, acl.Entity_ID,
vAG.Entity_Name, acl.Privilege_ID, tPriv.Code AS 'SecurityAttributeGroup_Code',
vAG.ID AS 'AttributeGroup_ID', vAG.Name AS 'AttributeGroup_Name',
vAGA.Attribute_ID,vAGA.Attribute_Name, tPrivA.Code AS 'SecurityAttribute_Code'
FROM mdm.udfSecurityUserAttributeGroupList(1, 4,149,1) acl
INNER JOIN mdm.tblSecurityPrivilege tPriv ON tPriv.ID = acl.Privilege_ID
INNER JOIN mdm.viw_SYSTEM_SCHEMA_ATTRIBUTEGROUPS vAG ON  acl.ID = vAG.ID 
INNER JOIN  mdm.viw_SYSTEM_SCHEMA_ATTRIBUTEGROUPS_ATTRIBUTES vAGA ON vAG.ID = vAGA.AttributeGroup_ID
INNER JOIN mdm.udfSecurityUserAttributeList(1, 4,149,1) acla ON acla.ID = vAGA.Attribute_ID
INNER JOIN mdm.tblSecurityPrivilege tPrivA ON tPrivA.ID = acla.Privilege_ID
ORDER BY vAG.Model_ID, vAG.Name

-------------------------------------------------------------------------------      
-- Attribute List User Security
-------------------------------------------------------------------------------
SELECT va.Model_ID, va.Model_Name, va.Entity_ID, va.Entity_Name,
va.Attribute_ID, va.Attribute_Name, tPrivA.Code AS 'Security_Code'
FROM mdm.udfSecurityUserAttributeList(5, 4,149,1) acla
INNER JOIN mdm.viw_SYSTEM_SCHEMA_ATTRIBUTES vA ON acla.ID = vA.Attribute_ID
INNER JOIN mdm.tblSecurityPrivilege tPrivA ON tPrivA.ID = acla.Privilege_ID
ORDER BY vA.Model_ID, vA.Entity_Name, vA.Attribute_Name