January 2017 Entries
Hide SQL server tables from users and only give them access through views

With self reporting becoming more and more popular, I am often asked how to give users access to data through views and not have them see the underlying tables.  This request often comes from the users themselves and not necessarily the administrators.  The users want to streamline how many objects they can see when they come into their reporting tools.

Even though the solution is spelled out in books, I find that the details still seems to escape many (even seasoned) dba's.  I am going to try to make it very simple.

As long as you are the owner of the objects that you are giving permission on, this is very easy. I am going to use Adventureworks in my example and I am setup as dbo in Adventureworks.  dbo is the owner of the tables and views that we will be working with for our proof of concept.

Because dbo owns both the tables and the views in Adventureworks, when a user who dbo gives permission to the view, inherits the rights to use the table data for the views… but the key here is that they cannot see the tables in their list of objects they have select permission on.

Here is the code to demo the functionality.  I used a role because then you can assign as many users as you want or swap users out but it does work the same way if you skip the role and just assign the permission to the user.:​


USE AdventureWorks

-- Create User
CREATE LOGIN [Jim] WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=[Adventureworks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [Jim] FOR LOGIN [Jim]

-- Create Role
CREATE ROLE [SelfReportingViews]

-- Add Jim to Role
ALTER Role [SelfReportingViews] ADD Member [Jim]

--try query as that user ** this should fail **
EXECUTE AS USER ='Jim'
-- this works
SELECT EmployeeID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, Phone, EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode, CountryRegionName, AdditionalContactInfo
FROM [HumanResources].[vEmployee]

Revert

-- just grant select to the role for the view
GRANT SELECT ON [HumanResources].[vEmployee] TO [SelfReportingViews]
GO
--try query as that user ** this should pass**
EXECUTE AS USER ='Jim'
-- this works
SELECT EmployeeID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, Phone, EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode, CountryRegionName, AdditionalContactInfo
FROM [HumanResources].[vEmployee]

-- that view is made up from joining several tables.  Try select on the tables to prove it doesn't work
SELECT * FROM HumanResources.Employee
SELECT * FROM HumanResources.EmployeeAddress
SELECT * FROM Person.Address
SELECT * FROM Person.StateProvince
SELECT * FROM Person.CountryRegion
SELECT * FROM Person.Contact

--switch back to dbo
Revert

-- set the system back
DROP USER [Jim]
DROP Login [Jim]
DROP Role [SelfReportingViews]

 

 

Visual proof of what they user will (or will not) see.

image  SNAGHTML3e97867

Add Comment Filed Under [ SQL ]