Now that RC0 has arrived I can finally post about security for tabular models. I have put off writing about security because I think the RC0 documentation on this subject is really quite good so I don’t have too much to add. Also, we had a minor feature add for RC0 that is very exciting. You can now create and delete roles in SSMS. Also, you can edit the row filters for a role in SSMS. The row filter editing functionality in SSMS is rather rudimentary (no autocomplete or semantic error validation), so I don’t recommend SSMS as your primary row filter editor, but for making small fix ups it works just fine.
Now on to the summary of the security model for tabular models for SQL Server 2012. There are two types of permissions for tabular models: database permissions and allowed row sets. A database permission is simple – a user can administer, read, or process a database. Granting a user read access to the database allows the user to see all data (but not metadata such as the DAX calculations included in the model). If you would like to restrict the amount of data the user in the read role can see, you can change the number of rows returned for a particular table by applying a row filter. Row filters affect related tables in the one to many direction, so applying a filter to a lookup table always affects the related fact tables. Aggregates in the table always aggregate the filtered row set. If multiple row filters are applied to a table, the user sees the intersection of the rows returned by each individual filter. If a user is a member of multiple roles, the user sees the union of all results returned for a particular role.
For more basic overview information, see the MSDN documentation on roles and Paul te Braak’s security overview post. The Create Roles tutorial shows you how to create basic roles and set row filters. There is an awesome new supplemental lesson for RC0, called Implement Dynamic Security Using Row Filters, that shows how to do dynamic security. This is exactly the demo I gave for SQL PASS on dynamic security, except on AdventureWorks. The lesson shows how to add a table with a list of securable objects, “secure” the table, and then modify the allowed rowset for users using the USERNAME() function. We do not have a meaningful example that shows how to use CUSTOMDATA() at this time.
Notes and tips: