locked
Hide Specific Views and all tables to Particular user in SQL Server 2008 r2 RRS feed

  • Question

  • Hi Friends , 

    I have a requirement, In our DB , we have  25 tables and 5 views .I have a few users in SQL server 2008 R2 . I have to give only 1 view access to a particular user  - XYZ . This user should not see any tables and other 4 views.He has to see only one view , say name like CustView with select access only.

    SQL login XYZ created and user also created with same name.

    could you please help me how to achieve it.

    Thank you ,

    Sravan

    Monday, May 22, 2017 4:54 AM

All replies

  • The user should not be a member of db_owner role , just issue

     GRANT SELECT on [dbo].[viewname] TO XYZ


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Davy_Wang Tuesday, May 23, 2017 12:10 AM
    Monday, May 22, 2017 5:35 AM
  • To expand upon Uri's comment, a database user can't see any tables or views, unless you go out of your way to grant the user some permission on them.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, May 22, 2017 3:35 PM
  • See it where?  Are you referring to an application interfaces, like SSMS or Access or Excel or something?

    Many applications use methods to return all objects in the database, even if the user does not have rights to those objects.  There is nothing you can do about those applications.  

    Monday, May 22, 2017 3:59 PM
  • Many applications use methods to return all objects in the database, even if the user does not have rights to those objects.  There is nothing you can do about those applications.  

    Eh, by default a user do not have permission to see any metadata. If you have SELECT permission on a table or a view, that implies VIEW DEFINITION on the object. But for an application to return information that the user has no permission to see, the application would be logging in with its own login and this login has been given too much powers.

    Monday, May 22, 2017 9:50 PM