Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Instant SQL Server Analysis Services 2012 Cube Security
  • Table Of Contents Toc
Instant SQL Server Analysis Services 2012 Cube Security

Instant SQL Server Analysis Services 2012 Cube Security

By : Satya Shyam K Jayanty
Buy this Book
close
close
Instant SQL Server Analysis Services 2012 Cube Security

Instant SQL Server Analysis Services 2012 Cube Security

By: Satya Shyam K Jayanty
Buy this Book

Overview of this book

SQL Server 2012 includes BI-specific features such as cube analytics, reporting-related features, multidimensional BI semantic models, and many more. This is a new addition that is of great use to Business Analysts, who can now build BI semantic models using the relational tables from the database engine rather than the dimensions from analysis services. Instant Microsoft SQL Server Analysis Services 2012 Cube Security is a practical, hands-on guide that provides clear, step-by-step recipes for getting started with cube security. As a Database Administrator and Data Architect, data security is of prime concern, and this book highlights the key concepts of securing cubes. By the end of this book, you will understand the SQL Server security model and how to effectively implement the same on the data platform. Security is paramount within a data platform, and thankfully, the issues surrounding data access can be fixed in the initial stages of its design. This allows you to avoid any breach of data access layers. The recipes in this book consist of the required ingredients to implement and administer OLAP & BI security management for your cubes. With this book, you will master the key aspects of implementing and managing security essentials and securing data layers. The recipes in this book incorporate best practice implementation, which include step-by-step instructions for creating models that implement security based on the currently logged on Windows user name or based on custom data passed to analysis services. The new BI platform features will help end users, business users, and technology users by providing a robust environment for IT, planning, budgeting, forecasting with scorecards, and reporting capabilities for the business users. These analytics will help end users to create a comprehensive management application to view the data with ease.
Table of Contents (7 chapters)
close
close
close
Instant SQL Server Analysis Services 2012 Cube Security
Icon
Instant SQL Server Analysis Services 2012 Cube Security
Credits
Icon
Credits
About the Author
Icon
About the Author
About the Reviewer
Icon
About the Reviewer
www.PacktPub.com
Icon
www.PacktPub.com
Preface
Icon
Preface
Lock Free Chapter
1
Instant SQL Server Analysis Services 2012 Cube Security
chevron up
Icon
Instant SQL Server Analysis Services 2012 Cube Security
Icon
Designing and creating security roles in an OLAP database (Intermediate)
Icon
Creating security roles (Intermediate)
Icon
Defining dimension access privileges (Advanced)
Icon
Managing role-based security – database to cube level (Advanced)
Icon
Managing role-based security – dimension to cell level (Advanced)
Icon
Accessing restrictions on dimensions and defining default members – full and partial (Intermediate)
Icon
Securing data at the cell level (Intermediate)
Icon
Preventing read/access to cell data (Intermediate)
Icon
Managing user-access privileges on a cube (Advanced)
Icon
Managing security roles for processing (Intermediate)
Icon
Data source and access control on cubes (Advanced)
Icon
Building a secure BI platform – the journey from SQL Server 2008 R2 to 2012 (Intermediate)
Icon
Securing key points in SSAS (Advanced)

Managing security roles for processing (Intermediate)

A user can be part of one or more roles in an Analysis Services database; the process to grant permissions across these roles is additive.

The SQL Server Analysis Services (SSAS) security is managed using roles, and the same is true for the AS database objects and data. By default, SIDs of Windows users and groups will have specific rights and permissions.

Again by default, the SSAS instance uses Windows Only authentication and no other authentication type. Within the Analysis Services there are two types of roles: server and database roles.

The server role is a fixed role that provides administrator access on the instance level. Similarly, the database role is defined by the administrators for access control to objects and data, which can be defined as read, read-contingent, or read/write purpose.

Note

The prerequisite for this recipe can be obtained from the Getting ready section of the Designing and creating security roles in an OLAP database recipe.

How to do it...

Perform the following steps:

  1. Start SQL Server Management Studio and connect to the SQL Server 2012 Analysis Services (SSAS) instance.

  2. Right-click on the SSAS instance and choose Properties to open the Analysis Server Properties page.

  3. Choose the Security option from the Select a Page pane that will present a list of server administrators as shown in the following screenshot:

    Note

    As we observe the privileges on the server level, we can click on the Add… button to add the required users or groups to grant access on a server wide basis.

Now let us work on the database level security settings that will dictate the data source security information.

  1. Expand the Databases folder, right-click on the AdventureWorksDM database, and then choose Properties.

    Note

    The default security settings on any SSAS instance are set as ImpersonateCurrentUser.

  2. To change the Data Source Impersonation Info setting, click on the button with three dots; the Impersonation Information page will be displayed. As per the default settings, the radio button selection is set on Use the credentials of the current user.

  3. For this recipe let us choose the Use the service account option.

  4. Step 3 will bring about the changes immediately without having to restart the SSAS instance by changing the Data Source Impersonation Info setting to ImpersonateServiceAccount.

  5. Click on OK to apply the changes on the AdventureWorksDM database.

How it works...

The relevant Analysis Services instance properties for the security page are used to specify the Windows users and groups included as members of the server administrator role for that instance.

The appropriate options at this level are to add or remove the NT Users and Groups, in order to enable them as server administrators. This means that the specified groups and users will have the highest privilege to perform any administrative action on the instance level. This will help to perform server-level administrative functions using the SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) that includes the database's creation or setting server level properties, and can be used for the programmatically administrative functions using Analysis Management Objects (AMO).

This server role cannot be deleted, permissions cannot be added or removed, and the specified user on this role is not an administrator for the instance of Analysis Services.

However, when it comes to the database level, the database properties dialog box will help us set the properties of a database. The data source impersonation information is the key factor to set for a relevant user to have the required privilege on a database-wide basis. This action specifies the default user credentials used to connect to data sources.

The impersonation options here will be specific to a Windows username (with password) service account, using credentials of the current user used to connect to the SSAS instance or default.

The database will be created as a separate object within the AS database and will only be applicable to the database in which that role is created. The permissions for this role may allow members to access or administer the database, in addition to to default access to objects and data.

By default, each permission has one or more access rights associated within that database, which in turn gives the permission finer control over access to that specified object in the database.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Instant SQL Server Analysis Services 2012 Cube Security
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon