December 29, 2005 at 5:14 am
HI,
I have the following requirments.
Their are 2 users (Say) A and B and a report called "Region".
If the user A logs into report manager and opens the report called "Region".Then the report should only display the data for the south region.
And if the user B logs in , then the same report should only display the data for the north region.
Could any body help me to solve this issue.
Thanks and Regards,
Chetan
December 29, 2005 at 5:47 am
If you run SELECT System_User it brings back the login name that you are currently logged in as.
You can create a table that maps System_User to region and then do a simple query to bring back the required records.
I would probably go one stage further and create a database role to region mapping. From sysusers you can work out what groups the system_user belongs to and therefore what regions they should be allowed to access. This method means that your roles can be set up and forgotten about, all you have to do is add or remove users from the roles.
December 30, 2005 at 7:37 am
Just to add a bit...
It is common to build a view that contains a JOIN to the role-permissions table and adds the extra criteria. The report query would then be built on the view.
You can either set the role in a parameter via the application if the database connection is shared amongst users or it can be determined within the stored procedure if the Windows identity flows into the database connection.
A view also allows users to write SQL statements if they are allowed and so inclined.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply