Solved

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

Posted on 2011-09-21
3
273 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 29

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

732 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