Friday, February 17, 2012

Database user permission

Add a new user to a database and assign permission only to 2 tables in this database for select/update/insert/delete.

the user role is Public.

But when you login in this database with SQL Query Analyzer using this user account, in Object Browser (left side), this user can see all info same as dbo, such as table name, column, data types,... although this user can not access the data for others tables.

How to limit the this user view in Object Browser and only see the tables that the user have proper permission on?

With SQL Server 2000, you could NOT limit the user. They can 'see' all objects in the database.

With SQL Server 2005, the user can only see the objects in the schema(s) they have permissions for.

Are you using SQL 2000?

|||Thanks for reply. Yes, I use SQL Server 2000|||

Unfortunately, with SQL 2000, all users can see all database objects with the client tools.

There is nothing you can do about it -except upgrade to SQL 2005. With SQL 2005, you can keep things private and undisclosed.