How to Revoke SELECT Permission for system_views To public

Question

I have the following T-SQL to display all the permissions granted to principals on my SQL server 2005:

select dp.NAME AS principal_name, --1
  dp.type_desc AS principal_type_desc, --2
  o.NAME AS object_name, --3
  p.permission_name, --4
  p.state_desc AS permission_state_desc --5
from    sys.database_permissions p
  left    OUTER JOIN sys.all_objects o
  on     p.major_id = o.OBJECT_ID
  inner   JOIN sys.database_principals dp
  on     p.grantee_principal_id = dp.principal_id
order by principal_name, object_name

The result displays public with SELECT granted:

 1      2             3            4      5
 ...
 public DATABASE_ROLE system_views SELECT GRANT
 ....

I think object_name system_views is for all the views in my database Views|system_views folder. I tried the following T-SQL (just to see if it works by GRANT again):

GRANT SELECT ON system_views TO public

I got error "Cannot find the object 'system_views', because it does not exist or you don't have permission". I do connect the SQL server as sa.

My question is how to revoke SELECT permission on system_views for public (user or principal?) and roll permission back if I have to. The second question is if the revoke on system_views for public have any side-effect for other users?

Answer 1

Hi,

select * from sys.system_views

Does public have VIEW DEFINITION on any of these?

I would highly recommend against mucking about with any of this.

You could just

DENY VIEW DEFINITION ON SCHEMA::DBO TO PUBLIC

Answer 2

public is a "special" role. Don't mess with it. Every user is a member of public by default, for example.

Metadata visibility actually determines what a user sees. So even if someone does SELECT * FROM sys.columns, they will see only the columns for objects they have rights on. No other rights = only info on the columns for system views.

You're likely to break stuff if you do this, especially in SSMS or direct access clients (Access, Excel etc)

Answer 3

There's no reason to revoke rights to view the system views. Users can only see the objects that they already have access to, so they already know those objects exist.

If you want to grant a user the right to see all objects in the database then grant them view definition on the schema or the database.