Wednesday, June 18, 2008 8:10 AM
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
Wednesday, June 18, 2008 8:17 AMDENY SELECT ON table TO user
GRANT SELECT ON view TO user
See how this goes for you.
Wednesday, June 18, 2008 9:13 AM
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:16 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).
Thursday, June 19, 2008 6:10 AM
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 7:33 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.
Friday, June 20, 2008 5:09 AM
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.
Thursday, June 26, 2008 7:39 AMhi
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........
Monday, May 14, 2012 11:24 AMAs Same How Did I Hide a database Like System Database?