4

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.

Jesse Taber
  • 2,376
  • 3
  • 23
  • 33

2 Answers2

6

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.

PJ8
  • 1,278
  • 10
  • 15
  • I had a "head slap" moment yesterday when I realized how easy it would be to do this with custom code similar to what you have there. It works great, either referenced/called from an external assembly or as embedded code within the report properties. No permissions issues yet. – Jesse Taber Nov 14 '08 at 16:15
  • 1
    =User!UserID Will display who is currently running the report as an expression – JsonStatham Jul 23 '12 at 12:17
  • 2
    The call to System.Security.Principal.WindowsIdentity.GetCurrent() failed for me due to permissions and trust level to the dll. Using System.Threading.Thread.CurrentPrincipal.Identity in its place did the trick: Dim Principal As New System.Security.Principal.WindowsPrincipal(System.Threading.Thread.CurrentPrincipal.Identity) – Jersey Dude Jan 17 '13 at 20:18
1

In Reporting Services just use :

Public Function IsMemberOfGroup() As Boolean

If System.Threading.Thread.CurrentPrincipal.IsInRole("MyADGroup") Then
    Return True
Else
    Return False
End If

End Function

as indicated in this posting

Note: This works once the report is deployed to server but not in the IDE.

Community
  • 1
  • 1
Ralph177
  • 201
  • 3
  • 5