TSQL to view, add and delete permissions

Question

I have created several new tables on a new database(Microsoft SQL server 2005). Now those tables are ready for client users to view through Access 2003 by using System DSN ODBC object (with SQL authentication). What I did was to create a Role and a schema in the database, and a login user for my SQL server default with my database and the schema name.

The problem is that clients not only can see tables I created but also system view objects in the database. I am not sure what should I do to setup a SQL login user, a schema and a role so that only my tables will be visible? Not sure why system view objects are visible to login users by default.

Another alternative way to clean up permissions I have right now. I did check some system view objects (a very long list). They all have permission with the schema I created some with public schema. Is there any way by using T-SQL script to view and then to delete permission associated with my schema for those system view objects? After that, I need to use similar scripts to add permission with my schema to the tables I created.

Answer 1

Well, by default, a new user will also be member of the "public" server and database role. The objects in the sys. and INFORMATION_SCHEMA schemas are visible to members of the public role.

You can either explicitly deny your own role access to those schemas, or you could remove the public role from your login user.

Marc