Solved

SQL Server hiding information_schema and sys views

Posted on 2009-07-15
1
650 Views
Last Modified: 2012-08-13
I have a user that is only assigned to the db role db_datareader and public. The user can see all the information_schema and sys views. As a test, I tried creating a separate role that denies view definition to the sys schema and removing the user from the db_datareader role, but the user can still see the views.

Any suggestions on how to deny view definition of the information_schema and sys views for a database?

The reason why I want to do this is because users do not want to see these extra views in their client applications when they are selecting user views/tables.

There does not seem to be a clear answer for this anywhere.
0
Comment
Question by:incidental74
1 Comment
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24865651
>> Any suggestions on how to deny view definition of the information_schema and sys views for a database?

No.. You cant deny usage of Information_schema and sys views to users.

The purpose of information_schema and sys views is to display records for those specific user only. It will display records for which the current user has access.

Hence if the user dont have access to an object, it will not display those records.
Since you added the user as part of datareader role, it will have select privileges on all tables and views and hence it will be visible in Information_schema and sys views.

You better need to REVOKE those objects to this user for the list of objects you wish.
That would help you in restricting the user to those objects.

Hope this clarifies.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question