Wednesday, February 13, 2013 9:43 AM
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
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
Wednesday, February 13, 2013 9:59 AMAnswerer
You need to create a view( one for each country) and grant the SELECT access to the user .
CREATE VIEW v_USA
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
- Marked As Answer by Arne Olsson Monday, February 18, 2013 7:26 AM