SecuringtheTabularBISemanticModel.docx

Securing the Tabular BI Semantic ModelSQL Server Technical ArticleWriter: Cathy Dumas, Business Intelligence Consultant, Avantage Partners. Contributors: Marius Dumitru, Akshai Mirchandani, Bob Meyers, Siva Harinath, Bradley Ouellette, Greg Galloway, Howie DickermanTechnical Reviewer: Nick Medveditskov, Edward Melomed, Owen Duncan, and Dave Wickert, Microsoft; Thomas Ivarsson, Sigma ABPublished: April 2012Applies to: SQL Server 2012Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.CopyrightThis document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. Some examples depicted herein are provided for illustration only and are fictitious.? No real association or connection is intended or should be inferred.This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. ? 2012 Microsoft. All rights reserved.ContentsCopyright2Introduction4Sample data files4Security model overview5Permissions5Connecting to tabular models8Impersonation9Creating roles9Using the Role Manager in SQL Server Data Tools10Testing roles10Deploying models with roles12Dynamic security13Modeling patterns for dynamic security14Example: Securing a model using the USERNAME() function15Example: Securing the model using the CUSTOMDATA() function24Example: Securing a model using parent/child hierarchies28Managing roles and permissions35Adding and removing administrators from a tabular instance35Using SQL Server Management Studio to manage roles36Using AMO to manage roles37Using AMO for PowerShell to manage ro