[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-09-21
3
Medium Priority
?
290 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 51

Accepted Solution

by:
Steve Bink earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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