May 5, 2011 at 2:42 am
Hi,
I have a requirement to control the data displayed in the report (SSRS 2008) based on the user logging in.
Eg: If the sales data is being shown in the report,the user should see the data for which he has access.
The security, which user has access to which region's data, is defined in the active directory.
There are different groups for each region and users having access to the data are made members of the appropriate group.
How do I connect to the Active directory from the report so that i can know which group the user belongs to?
Any help in this regard is much appreciated.
Thanks,
Ninu
June 19, 2011 at 5:51 pm
Hi Ninu
http://www.mssqltips.com/tip.asp?tip=1743
The parameter @REPORTUSER is defined in the Report Parameters as having a default value which is specified as the expression: =User!UserID SSRS sets the global variable User!UserID to the credentials of the user that is running the report. This allows us to filter our query based on who is running the report.
@REPORTUSER= @Domain/username
Best Regards
VJ
June 20, 2011 at 12:43 am
Are u refering to forms authentication on reports server ?
http://msdn.microsoft.com/en-us/library/cc281383.aspx
All users would login as browsers but it would be the administrators job to ensure only relevant users have permissions on a report.
Forms authentication allows users to connect to the report server after which the reports being displayed are a result of the users permission on the report.
June 20, 2011 at 7:46 pm
Hi Kurup
My Scenario as follows
State Sales managers should be see their State sales data based on the user logging
For Example : Vijay can see only State VIC data
CREATE TABLE [dbo].[SalesUserAccess](
[UserAccount] [nvarchar](50) NOT NULL,
[StateAccess] [smallint] NOT NULL
) ON [PRIMARY]
Data
StateCode Manager Name State
1 Jayanth NSW
2 Kurup ACT
3 Vijay VIC
CREATE TABLE [dbo].[ProductSales](
[StateCode] [smallint] NOT NULL,
[SalesAmount] [money] NOT NULL
) ON [PRIMARY]
Data
StateCode SalesAmount Manager name
1 750000.00 Jayanth
2 975000.00 Kurup
3 550000.00 Vijay
CREATE TABLE [dbo].[SalesUserAccess](
[UserAccount] [nvarchar](50) NOT NULL,
[StateAccess] [smallint] NOT NULL
) ON [PRIMARY]
GO
User Account State Access
domainname\Jayanth 1
domainname\Kurup 2
domainname\vijay 3
When State manager Vijay run this report. He will have following data
State Sales$ Manager Name State
3 550000.00 Vijay VIC
SELECT s.[StateCode]
,[SalesAmount],ManagerName,State
FROM [mssqltips_reporting].[dbo].[ProductSales] p inner join dbo.State s on s.StateCode=p.[StateCode]
where s.[StateCode] in (select stateaccess from dbo.SalesUserAccess where useraccount=@Reportuser
//Reporting services parameter name is @Reportuser
default value for @Reportuser=@UserID (Build in Field for domain user)
Best Regards
Vijay
June 20, 2011 at 7:51 pm
Hi Kurup
Scenario
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply