none
Hide tables in SQL Server

    Question

  •  

    Dear Techie's

     

       I want to give only VIEW  access instead of table acces to the external client on the database.

       So Is anybody having Idea about , How to Hide the table in SQL Server Vsersion (7,2007,2005) ?

       

    Thanks, in advance

        

     

    Samir

     

    Wednesday, June 18, 2008 8:10 AM

Answers

  • DENY SELECT ON table TO user

    GRANT SELECT ON view TO user

    See how this goes for you.

    Rob
    Wednesday, June 18, 2008 8:17 AM

All replies

  • DENY SELECT ON table TO user

    GRANT SELECT ON view TO user

    See how this goes for you.

    Rob
    Wednesday, June 18, 2008 8:17 AM
  • Hi Rob,

     

    That works ! Thanks for quick reply

     

    But how can i hide from viewing the Table structure. that's my next question

     

    Like in Sp_help or Sp_Help <tableName> the external user should not see the table list/structure

     

    Wednesday, June 18, 2008 9:13 AM
  • That's harder in SQL 7 & SQL 2000. In SQL 2005 though, tables to which you don't have access at all are hidden from view. Try removing all access to a table, and then logging in as that user to see what you can see (try right-clicking on the SSMS shortcut and saying "Run as" and entering someone else's details).

     

    Rob

    Wednesday, June 18, 2008 9:16 AM
  • Hi Rob,

     

    Thanks Again.

     

    The Project which i am working has Database SQL 7 and SQL 2000. The migration to SQL Server 2005 will not going to happen soon.

    So I can accpet harder way to Hide the Table till we migrate to SQL server 2005.

     

    Could you explain harder part please ?

     

    Thursday, June 19, 2008 6:10 AM
  • In my experience it's not worthwhile trying - if they don't have access to SELECT, INSERT, DELETE, UPDATE, they're not going to be able to affect much.

     

    However - you could remove the user from the datareader database role. You don't want to deny access to dbo.sysobjects, as it will probably stop things from working. You could deny access to syscolumns without breaking too much - but that doesn't stop people from knowing that a table exists. You could deny access to syscomments too... but that still doesn't solve your problem.

     

    Is it really that much of a requirement?

     

    At a push, you might be able to put all your data in a different database, with cross-database ownership chaining turned on. I'm not a fan of this, but... your ordinary user won't have access to that database except by accessing the objects which hook into them. I'm just talking off the top of my head - what you're asking isn't standard practice and I'm not even 100% sure this would work.

     

    Rob

    Thursday, June 19, 2008 7:33 AM
  • Hi Rob,

     

    That is too much to do in SQL 7 & SQL 2000 to hide access on tables. Let see what my boss days

     

    Thanks again for your suggestion and Help.Wink

     

    Samir

     

     

     

    Friday, June 20, 2008 5:09 AM
  • hi
    This is sanjay malani
    i have same problem  that i want to hide my database srtcture from end user..................... how it can possible ??
    thansk in advance plz help me out........
    Thursday, June 26, 2008 7:39 AM
  • As Same How Did I Hide a database Like System Database?
    Monday, May 14, 2012 11:24 AM