Lesson 1: Setting Up Permissions for this Tutorial
Before you can follow this tutorial, you will need to set up the computer that you are using for this tutorial. In addition, you will need to create an attribute that changes the login information for some fictional Adventure Works Cycles employees so that you can set row-level security permissions. Typically, you would not need to perform any of the steps in this lesson when setting up your own security filters because your group and user permissions and login accounts will already be set up, and your report model will already be deployed.
Note
This tutorial assumes that the server and client computers being used for this tutorial are the same computer.
To set up your computer for this tutorial, you will need to do the following in this lesson:
Create two temporary user accounts on the computer that you are using for this tutorial using the Computer Management tool. The fictitious users, Rachel Valdez (Rachel0) and Garrett Vargas (Garrett1) already exist in the AdventureWorks2008R2 sample database. You will use these temporary user accounts in a later lesson to see the results of this tutorial.
Grant these fictitious users permission to access the report server and the Report Manager by assigning them catalog roles and system roles.
Modify the report model and deploy it to the report server. To accurately show how security filters work, you will need to create a new field that maps fictitious users from the AdventureWorks2008R2 database to the temporary user accounts you created on your computer.
Create a new login that the report model will use to access the AdventureWorks2008R2 database.
Important
These settings are created for the purposes of this tutorial only. Your SQL Server production environment should be set up differently and, therefore, the steps in this lesson are not required when setting up security filters. Remember to change these settings back after you complete this tutorial.
To create computer user accounts
Click Start, point to Control Panel, point to Administrative tools, and then click Computer Management.
In the Computer Management (Local) pane, double-click Local Users and Groups.
Right-click the Users folder and then select New User.
The New User dialog box opens.
In the User name box, type Rachel0.
In the Full name box, type Rachel Valdez.
In the Description box, type Account used for the Model Security tutorial.
In the Password box, type Pass12,Word.
Note
To help keep your computer more secure, you should always use a strong password. A strong password should be at least seven characters long, and contain characters from the following three groups: Letters, Numerals, and Symbols.
In the Confirm password box, type Pass12,Word.
Clear the User must change password at next logon check box, and then click Create.
Rachel0 is added to the list of users. The New User dialog box remains open.
In the User name box, type Garrett1.
In the Full name box, type Garrett Vargas.
In the Description box, type Account used for the Model Security tutorial.
In the Password box, type Pass12,Word.
In the Confirm password box, type Pass12,Word.
Clear the User must change password at next logon check box, and then click Create.
Garrett1 is added to the list of users.
Click Close.
Next, you will give these users permissions to the report server.
To assign catalog roles to the new users
To start Report Manager, start Microsoft Internet Explorer 6 or later.
In the Address bar of the Web browser, type the Report Manager URL. By default, the URL is http://<ComputerName>/reports.
Select the Properties tab.
Click New Role Assignment.
Note
A role assignment specifies the tasks that the user or group can perform to an item on the report server.
In the Group or user name box, type <computername>\Rachel0.
Select the Browser and Report Builder check boxes.
Click OK.
Click New Role Assignment.
In the Group or user name box, type <computername>\Garrett1.
Select the Browser and Report Builder check boxes.
Click OK.
To assign system roles to the new users
In Report Manager, click Site Settings on the global toolbar.
The Site Settings page appears.
Note
If Site Settings is not available, you do not have permission to access site settings and need to contact your administrator.
In the Security section, click Configure site-wide security.
Click New Role Assignment.
In the Group or user name box, type <computername>\Rachel0.
Select the System User check box.
Note
The system role gives the user or group access to Report Manager. The roles describe the tasks or actions that can be performed.
Click OK.
Click New Role Assignment.
In the Group or user name box, type <computername>\Garrett1.
Select the System User check box.
Click OK.
The fictitious users that you created are now able to access items on the report server as well as log on to the computer on which the report server is running.
To start SQL Server Management Studio
Point to Start, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
The Connect to Server dialog box appears. If it does not, in Object Explorer, click Connect and then select Database Engine.
In the Server type list, select Database Engine.
In the Server name list, select the database server that you are using for this tutorial.
Click Connect.
The SQL Server Management Studio window opens. Next, you will create a login to the AdventureWorks2008R2 database so that the model can use this fictitious login to access the database.
To create a security login
In Object Explorer, expand the Security node for the server you specified.
Right-click Logins and select New Login.
The Login - New dialog box opens.
In the Login name box, type TutorialLogin.
Select the SQL Server authentication option.
In the Password box, type Pass1word.
Note
To help keep your computer more secure, you should always use a strong password. A strong password should be at least seven characters long, and contain characters from the following three groups: Letters, Numerals, and Symbols. Also, make sure that you delete this Login after you have completed the tutorial.
In the Confirm password box, type Pass1word.
Select the Enforce password policy check box.
Clear the Enforce password expiration check box.
In the Default database drop-down list, select AdventureWorks2008R2.
Click OK.
The TutorialLogin is added to the Logins list. The fictitious users that you created earlier in the lesson will use this login to access the AdventureWorks2008R2 database. Next, you need to assign db_datareader privileges to the login.
To create an AdventureWorks2008R2 Security user
In Object Explorer, expand the Databases node for the server you specified.
Expand the database node for AdventureWorks2008R2.
Double-click Security.
Right-click Users and then click New User.
The Database User - New dialog box opens.
In the User name box, type TutorialLogin.
In the Login name box, type TutorialLogin.
In the Default schema box, type db_datareader.
In the Database role membership area, select the db_datareader check box.
Click OK.
Next, you will open the report model sample in Business Intelligence Development Studio, create a new attribute, modify the data source login information to use the new login, and then deploy the model.
To open the report model sample
Point to Start, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click Business Intelligence Development Studio.
On the File menu, point to Open, and then click Project/Solution.
In the Open Project dialog box, navigate to the location where you installed the SQL Server 2008 R2 samples. By default, the samples are installed in the following location: C:\Program Files\Microsoft SQL Server\100\Samples.
Double-click Reporting Services.
Double-click Model Samples.
Double-click Adventure Works Model.
Select Adventure Works Model.sln and then click Open.
In Solution Explorer, double-click Adventure Works.smdl.
The report model sample opens in Model Designer. Next, you will create a new expression that replaces the database login information with the login information you set up earlier in this lesson.
To create an attribute
In Model Designer tree view, right-click the Employee entity, point to New, and then click Expression.
The Define Formula dialog box opens.
Click the Functions tab and then expand the Text functions node.
Double-click the Replace function.
The function is added to the formula box.
Click the Fields tab.
In the Fields list, double-click Login ID.
In the formula box, find is replaced with Login ID.
In the formula box, select replace and then type "adventure-works".
Note
Adventure-Works is the login information that you need to replace. Make sure to include the double-quotes.
In the formula box, select string and then type "ComputerName".
Note
This is the name of the computer that you used to set up accounts for Rachel0 and Garrett1. Make sure to include the double-quotes. Your expression should appear as: REPLACE(Login_ID, "adventure-works", "<computername>").
Click OK.
The Define Formula dialog box closes.
Right-click the NewExpression attribute, click Rename, and then type Login ID2.
You will use this new attribute when applying your row-level security filter in the next lesson.
Select Login ID2.
In the Properties pane, locate the Nullable property.
Click the drop-down arrow and select True.
The Replace function can return Null; therefore, the Nullable property must be True.
To modify the data source connection
In Solution Explorer, double-click the Adventure Works.ds file.
The Data Source Designer dialog box opens.
Click Edit.
The Connection Manager dialog box opens.
Select the Use SQL Server Authentication option.
In the User name box, type TutorialLogin.
In the Password box, type Pass1word.
To verify the connection, click Test Connection.
Click OK and then click OK again.
To exit the Data Source Designer dialog box, click OK.
The report model can now be deployed.
On the File menu, click Save All.
Note
If you already have an Adventure Works.ds file deployed to the report server, the credentials will not be updated unless you change the default property. To change the default property, right-click the Adventure Works Model project and select Properties. In the Adventure Works Model Property Pages dialog box, click the OverwriteDataSources drop-down list and select True.
To deploy the report model sample
In Solution Explorer, right-click the Adventure Works Model project and then click Deploy.
The model is deployed to the report server. Leave Business Intelligence Development Studio open with the model displayed.
Important
To verify that the model was deployed successfully, you can view the Error List tab. If an error occurs, you will need to troubleshoot the problem before continuing.
Next Steps
The user accounts, permissions and customized login are now set up on your computer so that you can successfully complete this tutorial. You have created two fictitious users and assigned them the appropriate catalog and system role permissions on the report server. You have deployed the report model sample, and created a customized login which the model will use to read the AdventureWorks2008R2 database. Later, you will use this fictitious user to assign row-level security.
Important
After completing this tutorial, remember to remove these user accounts from the local computer and remove all permissions that you have granted.
In the next lesson, you will open the Adventure Works report model sample and create a default security filter in Model Designer. See Lesson 2: Creating Attributes for Row-Level Security.