Solved

system objects permissions to public  - issue in 2005/8?

Posted on 2011-09-21
3
269 Views
Last Modified: 2012-08-14
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 36577976
You might be better off asking specific questions, even if you have to get the excerpt from that link.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 36578423
I would not describe this as a problem so much as an aspect of the server environment of which, as an administrator, you need to be aware.  The public role is SQL Server's equivalent of the 'Everyone' group in NTFS permissions.  It is a default role, and every login/user is automatically added to it and cannot be removed.  However, it is not a fixed role in the strictest definition, since you can alter the permissions assigned to it.  To quote MSDN on MSSQL2008 (emphasis added):

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

So what permissions does it have by default?  Another page has this to say:

The public server role is granted VIEW ANY DATABASE permission and the CONNECT permission on the default endpoints (TSQL Local Machine, TSQL Named Pipes, TSQL Default TCP, TSQL Default VIA).

The VIEW ANY DATABASE permission exposes a fair bit of information.  You can see some specifics in Metadata Visibility Configuration, but I don't think this is going to be a comprehensive list.  This permission could lead to other item availabilities you won't know about until you discover them buried down in some sys.sp* or dm_* BOL notes.

As mentioned before, and noted in your referenced PDF, you can change these permissions.  However, realize that *every* user is part of this group, so changes you make to it have the potential to be very destructive to your security.  Any DENY settings will take precedence.  You can REVOKE permissions (just remove a GRANT, versus an active DENY), but then you run the risk of a given user not having, for example, SELECT privileges on a system table they need.

Personally, I've never liked the way every database is exposed to every login in MSSQL, but it is a fact of life when using their technology.  MySQL allows for that level of granularity, so I've never accepted that it just can't be done.  But again, it is what it is.  If you absolutely need to know if a particular piece of meta-data is exposed to the public role, my best advice is to try it yourself.
0
 
LVL 5

Author Comment

by:25112
ID: 36579197
thanks for the clarification..


sammySeltzer, i will remember your suggestion, too...
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 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