How can I determine what AD security groups the current user is in from within an SSRS report?

Question

I have a need to determine what security group(s) a user is a member of from within a SQL Server Reporting Services report. Access to the report will be driven by membership to one of two groups: 'report_name_summary' and 'report_name_detail'. Once the user is executing the report, we want to be able to use their membership (or lack of membership) in the 'report_name_detail' group to determine whether or not a "drill down" should be allowed.

I don't know of any way out of the box to access the current user's AD security group membership, but am open to any suggestions for being able to access this info from within the report.

Answer 1

You can add custom code to a report. This link has some examples.

Theoretically, you should be able to write some code like this, and then use the return value to show/hide what you want. You may have permissions problems with this method, though.

Public Function ShouldReportBeHidden() As Boolean
Dim Principal As New System.Security.Principal.WindowsPrincipal(System.Security.Principal.WindowsIdentity.GetCurrent())
If (Principal.IsInRole("MyADGroup")) Then
    Return False
Else
    Return True
End If
End Function

Alternatively, you could add your detail report as a subreport of your summary report. Then you could use the security functionality built in to SSRS to restrict access to your sub report.

Answer 2

I have tried this and it works great in the Visual Studio Development environment but once the report is published to SSRS.

Public Function DataAccess() As String Dim Principal As New System.Security.Principal.WindowsPrincipal(System.Security.Principal.WindowsIdentity.GetCurrent()) If (Principal.IsInRole("Chris")) Then Return "Admin" Else Return "Not Admin" End If End Function

This function checks to see if the user is in the 'Chris' security group and returns 'Admin' if true.

Running the report in SSRS, #ERROR is displayed instead of 'Admin' or 'Not Admin'

This is a security problem somewhere I'm sure. Any idea what to do to make this work???

Thanks Alan