MS SQL Server 2012 Master Data Services – list all user / group permissions using TSQL

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:

http://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.

[twitter-follow screen_name=’dangalavan’ show_screen_name=’no’]

Share This:

One thought on “MS SQL Server 2012 Master Data Services – list all user / group permissions using TSQL

Leave a Reply