Deny View in SSMS but Use View for SSRS

  • Hi Everyone,

    This is bit of a complicated scenario, so I will start the from the beginning.

    Here is our situation.  We have one SQL server, with one instance.  Within this instance we have several databases.  A few of these databases contain data that is quasi-shared by a few sister organizations  The head organization will also have a database in which we will create views that will feed our common SSRS portal.

    Also, each organization gets their own database to create views based on these 'shared' databases by select users (e.g. Decision Support) but most of the data will be shared through an SSRS portal that's AD authenticated.

    The SSRS reports will be built so that the report's data will be filtered to display only that organization's data only, even though the view contains the data for all organizations.

    Since the SSRS reports are being fed from views from the Head Organization, we have to allow those users from the sister organizations read and execute permission to those unfiltered views.

    So for example, we have three databases.  DB1 holds all of the data, DB2 is used by the head organization to create views for the SSRS.  DB3 is for one of the 'sister' organizations.  When Johnny from the sister organization runs a report through SSRS, the data is being filtered via SSRS and being queried from DB2 (the head organization's db).  If Johnny logs into SSMS, then he would have access to the DB2 views (unfiltered) and his own DB3.

    My question is - if a user somehow got access to a copy of SSMS and knew the address of the server, they could log into SSMS and view that unfiltered view.  Is there a way from preventing that from happening?  I know that those are pretty big 'ifs' but I am still curious.  I can't think of any way from stopping that.

    Please let me know if this makes sense or need more clarification.  Thanks!

  • Thanks Ken - that's an interesting thought!  I guess my only concern is that if we have had 100 users accessing this view, then the procedure described in your link would have to be re-created and modified based on the view (and there would have to be organizational specific data in each row to apply this?).  Could Row Level security be based on groups of users?

  • Row Level Security would usually be based on AD groups or Roles.

    While your problem description is vague I suspect Row Level Security is not the best option here due to the fact it is not bullet proof. What I would look at doing is altering your SSRS reports so that they only use stored procedures so the users require no SELECT permissions.  The stored procedures can then implement any security filtering you require instead of using filter predicates in RLS. Of course if you have thousands of reports this could be a large undertaking. Stored procedures will also not work if users are allowed to create ad hoc reports in which case RLS may be the best option.

    ps Another advantage of using stored procedures in SSRS is that you can log who/when sensitive reports are run.

    • This reply was modified 1 week, 6 days ago by  Ken McKelvey.
    • This reply was modified 1 week, 6 days ago by  Ken McKelvey.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply