Solved

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

Posted on 2011-09-21
3
262 Views
Last Modified: 2012-08-14
0
Comment
Question by:25112
3 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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
Comment Utility
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
Comment Utility
thanks for the clarification..


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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
SQL Server 2016 Developers Edition Install 4 56
SQL JOIN 6 31
replication - alerts? 4 19
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now