Setup permission table to secure data

Question

Hi!

I would like to setup a permission table in SQL from which i would like to decide which user has permission to what so i can use this as filters in report manager based on each users id.  From my example below i would like to setup a permission table that secures that user A gets access to sales only for France, B for USA E for Norway and USA and so on and the user shoul only have datareader roles. My question is how to setup such table and how to make it work when a user goes in to report manager to wiew a sales report that shows sales for all this tables. I think i know how to set the filter in a stored procedure to make this work but i dont really know how to do the initial setup of the table and how get the connection between user id in my permission table and the userid that is working in report manager. I also wonder if it is neccesary to setup new usergroups in the sql database sto make this work? Is there someone that have any easy example that explains how to make this work?

Hope i sent this question to correct forum

Best regards

Arne

PermissionTable	
UserId	Country
A	France
B	USA
C	Norway
D	France
E	Norway
E	USA
	
Sales table 	
Country	Sales
France	10
USA	30
Norway	20

---

Arne Olsson

Answer 1

You need to create a view( one for each country) and grant the SELECT access to the user . 

CREATE VIEW v_USA

AS

SELECT col1 FROM trbl WHERE Country='USA'

GRANT SELECT on v_USA TO user

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance