While implementing Master Data for a company in the finance industry, some limitations around the Web permissions interface became an issue. How could I get a list of all users / Active Directory Groups and their permissions per master data entity. The MDS web UI will display permissions for one user or group at a time, however how about the full listing of all users and all associated permissions?
So it was time for the succinct SQL Profiler to see what the Web UI was doing behind the scenes. A solitary stored procedure was pinpointed: mdm.udpSecurityPrivilegesSummaryGet !
It transpired that tying this with the mdm.tblSecurityAccessControl and mdm.tblUser tables would work wonders! And a cursor for good measure! Follow this link for the full scrip, the results can be integrated with e.g. Reporting Services or Integration Services:
https://www.galavan.com/sql/GetPermissionDetailsForAllMDSUsers.txt
Please note that this is an undocumented stored procedure and as such is subject to change with future releases of the product, should be used only as a quick fix.
It was a helpful post. Thanks…Secretarial Software