locked
view access thru SSRS but deny on SSMS RRS feed

  • Question

  • I have two views = securedview1 and secureview2. When a user try to access these views thru SSRS then should be able to acceess but when a user try to access these views using SSMS, system shouldn't allow.

    Users are part of AD group: ADGROUP1

    How to do?


    • Edited by kdinuk Monday, November 25, 2019 7:07 PM
    Monday, November 25, 2019 7:07 PM

All replies

  • There is nothing built in which would restrict access based on application.  A user either has access to SQL Server or does not.

    If you are using SQL 2016+, you could use RLS to filter rows returned by the view based on "application name".  However, the query against the view will still run.  It would just return 0 rows.  This may be confusing to users.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

    Monday, November 25, 2019 7:22 PM
  • Furthermore, the application name is something the users can set themselves when they connect with SSMS.

    So the answer to the question is: can't be done.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 25, 2019 10:56 PM
  • I forgot to mention that we are connecting SSRS using service id and impersonating that id. For this, we have added ad group to sql server But our requirement is to restrict users not to connect SSMS.
    Tuesday, November 26, 2019 2:12 AM
  • Hi kdinuk,

    Your requirement can’t be achieved. If you want to implement restrictions on data row access, I suggest you using RLS as Tom mentioned.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 26, 2019 9:02 AM
  • I forgot to mention that we are connecting SSRS using service id and impersonating that id. For this, we have added ad group to sql server But our requirement is to restrict users not to connect SSMS.
    If SSRS connects with a proxy account and as the actual user, it's simple. Don't add the users as logins to SQL Server at all. Or if it is only a matter of these views, don't grant access to them.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 26, 2019 10:15 PM