[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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

0
25112
Asked:
25112
1 Solution
 
sammySeltzerCommented:
You might be better off asking specific questions, even if you have to get the excerpt from that link.
0
 
Steve BinkCommented:
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
 
25112Author Commented:
thanks for the clarification..


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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now