Master Data Managment; a succinct explanation

Master Data Management (MDM) is one of those terms that have been hovering around in the data management background for a number of years. However, it looks like its time is drawing near: the next version of SQL Server will feature MDM capabilities out of the box, a move that should bring the associated techniques and technologies into the limelight.

I could go on, but I came across this article by the blessed Kirk Hasleden, author of the peerless "Microsoft SQL Server 2005 Integration Services", which explains the basics - and why it's so important - quickly and clearly. I suspect that this won't be the last posting on these pages on this topic.

No naming names

…but a certain Very Large And Respected Accounting Firm was conducting an audit at a client recently, and I was asked to provide screenshots “proving” that access to certain financial information on their SQL Server databases was restricted, that only a certain number of people had permissions to create or alter SQL views used for reports.

I could understand the motivation behind the request, but the idea that this could be somehow proved by taking a couple of screenshots is pretty…um…alternative.

Firstly and most obviously, it’s easy to change role memberships and permissions assignments in the appropriate dialogs temporarily to show the auditors what they want to see, then change them back after the screenshots are made. Secondly, SQL Server provides you with a myriad of levels and means to enforce or apply permissions and restrictions.

I can say that all QueryDevelopers are members of db_owner, for instance, to give them all permissions on all objects within a given database. Or I can GRANT ALTER VIEW TO QueryDevelopers to allow them to change any view definition within the database. Or I can GRANT ALTER ON OBJECT::dbo.vwEndOfYearBalances TO QueryDevelopers to limit the permissions to a specified object.

There’s also nothing to stop me doling out permissions on an individual basis, instead of to groups or roles, so you can see that a couple of screenshots are not going to cover the case, particularly if you want to “prove” a negative – who isn’t allowed to change object definitions (or read data, or whatever)?

I hope and trust that our friends at the VLARAF check the bookkeeping records a little more thoroughly than they do SQL permissions. You’d think that they’d have a standardized query to spit out the effective permissions of each user or group in a database or on a database server, wouldn’t you? I sense a gap in the market…