Blog Title: Limiting Table Access for Reporting Part 2

In the first blog post of this series, I showed you how to create a database schema and how to create a view. In this post, I will wrap it all up and show you how to create a SQL Server login account. I will also show you how to give them access to the view that we created in the Reporting schema. If you want to follow along and you haven’t read part 1, you can read it here.

Like in part 1, I’m going to fire up SQL Server Management Studio (SSMS). In this first step, we are going to create a SQL Server login account called IronMan. Navigate to the database engine and then to the Security folder as shown in figure 1 below. Right click on the Logins folder and select New Login…

Figure 1: Security > Logins > New Login

Next, enter your login name, select SQL Server Authentication and enter a password for the IronMan user account. Select IronMan’s default database and then click on the User Mapping tab as shown in figure 2.

Figure 2: Create SQL Server Login

In the User Mapping tab, select the database you want to give IronMan access to. AdventureWorks in this case! Set his Default Schema to Reporting as created in part 1 of this series as shown in figure 3 below.

Figure 3: New Login – User Mapping

Now that we have a database user to grant access to, let’s open up IronMan’s database login. Navigate to the Security folder and Users for AdvenureWorks database as shown in figure 4 below. Double click on IronMan or right click and select Properties.

Figure 4: Database User Security

Now, we need to lock our IronMan database user down! Click on the Securables tab and then click the Search… button to limit him to the Reporting schema as shown in figure 5 below.

Figure 5: Lock IronMan Down

Click OK and now IronMan should only see the view that we gave him access to view. To test, connect to a new instance of SSMS using IronMan’s credentials. The results should be something like displayed in figure 6 below. Notice that he cannot see any other tables in the AdventureWorks database.

Figure 6: IronMan’s Permissions

Now, there are other ways to lock users down and this is just one representation of how one could achieve this. It is not meant to be “the only” way.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter