Designing and creating are the initial steps to define the security roles, irrespective of the type of database, whether it is a relational-database or analytical-database.
Note
A primary reflection of security roles in a database highlights the behavioral changes of users and schemas separation since SQL Server 2005 version.
In a relational world, schemas are no longer equivalent to database users (they are simply a container of objects) and each schema has a distinct namespace that exists independently of the database user who created it. Such separation enables the administrators to grant necessary privileges on schemas to the corresponding database users.
Similarly, security roles also play very important role in the SSAS world. To manage security for Analysis Services objects and data, a role can be associated to the Security Identifiers (let us refer to them as SIDs) of Windows operating system users and groups that have specific access rights and permissions defined for the objects managed by SSAS instance.
Let us see how to construct security management and fulfill security roles criteria from the first recipe in this instant cookbook.
In order to fulfill the criteria we need the following prerequisites and steps:
SQL Server 2012 Management Studio (SSMS)
AdventureWorks2012 database: we can obtain the necessary database files and database product samples from SQL Server Database Product Samples downloads page http://msftdbprodsamples.codeplex.com/releases/view/55330.
Ensure you install the databases to your specified 2012 version instance
For the sake of this book I have created a new OLAP database using the
AdventureWorksDM.xmlascript file
In order to create security it is essential to create a Windows user and group that will enable the remaining recipes in this book to proceed. Here we will browse through the Windows Server 2008 R2 operating system options.
Once the prerequisites mentioned in the preceding Getting ready section are completed, follow these steps:
Navigate to Start | Control Panel and choose Administrative Tools, then open the Computer Management (Local) program.
Expand the
Local UsersandGroupsfolder.Right-click on Users to create a new user. For the sake of this recipe I have created a user named
DBIA_BI_User.Right-click on Groups to create a new group, name the group as
BI_User, add the newly created user DBIA_BI_User, and click on OK.To relate to the preceding steps you should have similar properties shown as per the following screenshot:
Now the initial requirement of a local user and group on the server is completed, let us jump into SQL Server Management Studio that is specific to Analysis Services instance security.
Open the SSMS and choose Connect to Analysis Services… (AS) instance.
Right-click on the selected AS instance and choose Properties.
Within that Properties page select the Security option that will show the default Server administrators.
The preceding steps will be the starter steps to design and create security roles in an OLAP database.
The steps to create a specific security role within the analytical database area needs elaboration, as it is different from the Server role and the Database role from the relational database world. Similarly, security roles also play a very important role in the SSAS world. To manage security for Analysis Services objects and data, a role can be associated to the SIDs of Windows operating system users and groups that have specific access rights and permissions defined for the objects managed by SSAS instance.
The Analysis Services instance property pages from SSMS will show (and contain) a subset of properties (by default) that are likely to be modified. In order to obtain a full set of properties, click on the Show Advanced (All) Properties checkbox at the bottom of the server properties page. Similarly, a full set of properties can also be found in the msmdsrv.ini file.