Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Security Filter Causes Incorrect SQL

Security Filter Causes Incorrect SQL

Security Filter Causes Incorrect SQL

I've got a strange situation with a report that I have created.  Under my login which does not have any security filters, the sql for the report is correct:

select a11.SBU  SBU,
    a11.Company  Company,
    a11.ReservationCarrier  ReservationCarrier,
    a11.ReservationOrigin  ReservationOrigin,
    a11.ReservationDestination  ReservationDestination,
    a11.CardType  CardType,
    a12.Month  Month,
    a12.MonthName  MonthName,
    a12.CalendarYear  CalendarYear,
    sum(a11.DetailDeposit)  WJXBFS1
from Res_Summary_Accounting_Detail a11
    join Dim_Date_Reservation_Depart a12
      on  (a11.ReservationDepartDate = a12.ReservationDepartDate)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
 and a11.CancelDate is null)
group by a11.SBU,

Under a user's login that does have a security filter on the SBU attribute, the sql for the report is incorrect.  Notice how the sql performs a distinct select, leaves out the sum on the metric and needlessly joins to all of the dimension tables:

select distinct a11.SBU  SBU,
    a11.Company  Company,
    a11.ReservationCarrier  ReservationCarrier,
    a11.ReservationOrigin  ReservationOrigin,
    a11.ReservationDestination  ReservationDestination,
    a11.CardType  CardType,
    a14.Month  Month,
    a14.MonthName  MonthName,
    a14.CalendarYear  CalendarYear
from Res_Summary_Accounting_Detail a11
    join Dim_SBU a12
      on  (a11.Company = a12.Company)
    join Dim_Carrier_Reservation a13
      on  (a11.ReservationCarrier = a13.ReservationCarrier)
    join Dim_Date_Reservation_Depart a14
      on  (a11.ReservationDepartDate = a14.ReservationDepartDate)
    join Dim_City_Reservation_Destination a15
      on  (a11.ReservationDestination = a15.ReservationDestination)
    join Dim_City_Reservation_Origin a16
      on  (a11.ReservationOrigin = a16.ReservationOrigin)
where (a11.ReservationDepartDate = CONVERT(datetime, '2006-03-07 00:00:00', 120)
 and a11.ActivityType = 'DEP'
 and a11.SBU in ('ADV', 'AMV'))

Has anyone ever seen this situation?  I've tried changing the dimensionality on the metric to no avail.  If I run the report under my login and answer the SBU prompt to essentially replicate what the user's security filter is doing automatically, the sql still looks good.  I just don't understand what the security filter is doing.  The SBU attribute used by the security filter looks fine.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close