How to limit a user to only see some of the tables in the DB?

Question

Hello all,

Currently, I don't want a user to see all the tables or views, I only want to let he see some of them, how to achieve this? Thanks in advance.

---

Thanks, Jed deng

Answer 1

You can create tables/views etc in a different schema and segregate permissions based on the schema. The default schema is [dbo].

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 2

You can grant  'SELECT' permissions to him\her only to the tables they are allowed to see.

Answer 3

Note that the permissions to see the names of tables changed. SQL Server 2000 let you see lots. SQL Server 2005, 2008, and 2012 only let you see the table if you have some sort of permission on it. So what version of SQL Server are you using?

---

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

Answer 4

On 2005 forward:

As Rick pointed out, there are view metadata restrictions.  However, if the database has granted VIEW DEFINITION to the user he can see everything.  If you deny VIEW DEFINITION on the database, they will only be able to see the definition of tables, views, etc, to which they have been granted permissions.  

Note that granting permissions does not just mean granting directly to the user, but includes all groups or roles for which the user is a member.  For example, this includes the public role.

Details on VIEW DEFINITION and VIEW ANY DEFINITION: http://msdn.microsoft.com/en-us/library/ms175808(v=sql.105).aspx

RLF

Answer 5

You can grant  'SELECT' permissions to him\her only to the tables they are allowed to see.

Hi Pradeep.c

Could you please show us how to grant select permissions to him\her only to the tables they are allowed to see?

---

Thanks, Jed deng

Answer 6

The simplest thing to do the following for each table and login combination.  E.g.

GRANT SELECT ON dbo.MyTable TO [Domain\Login1]  
GRANT SELECT ON dbo.MyTable TO [Domain\Login2]  

But this can get tedious for many logins, so I prefer to do as follows:

USE WorkDB
GO
CREATE ROLE SelectXYZTables
GO
GRANT SELECT ON dbo.MyTable1 TO SelectXYZTables  
GRANT SELECT ON dbo.MyTable2 TO SelectXYZTables 
-- Do grant for all needed tables and views.
GRANT SELECT ON dbo.MyTable25 TO SelectXYZTables  
GO
-- Repeat for each user who should have these right.
-- This code assumes that the needed server logins are already added.
CREATE USER [NewGuy] FOR LOGIN [NewGuy] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'SelectXYZTables', N'NewGuy'
GO
CREATE USER [NewAssistant] FOR LOGIN [Domain\NewAssistant] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'SelectXYZTables', N'NewAssistant'
GO

This way a user's rights can be changed by adding or removing him from membership in the role, rather than granting orrevoking every detailed permission. 

RLF

Answer 7

Great, thanks RLF very much!!

---

Thanks, Jed deng