Re: How can I remove Public access to SQL Express db's?

The guest user is a pre-defined user that exists in all databases. Guest has no CONNECT permission in user databases by default (SSMS Express might hide this). You can't delete guest but can render it unusable by revoking CONNECT permission in databases other than master and tempdb. Guest must have CONNECT in master and tempdb system databases and inherits minimal permissions from the public role membership.

I suggest you leave the guest user alone unless you have a specific reason to do otherwise. Also, its a good practice not to grant additional permissions to guest or public and instead create your own roles for security management.

See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"brad" <piraparana@xxxxxxxxxxxxxxxx> wrote in message news:58B84E2A-6713-478D-861E-9EB28E429594@xxxxxxxxxxxxxxxx
Well, first of all I'd like to say that the Studio Express is a nice tool -
thanks for telling me about that. It turns out that the guest account is not
listed at all for my database, but it is listed for the default databases
(model, msdb,tempdb), with no permissions (neither grant nor deny). Should I
just remove the guest acount or leave as-is?


--
bb


"Adams Qu [MSFT]" wrote:

Hello,

Thank you for posting here.

To remove the public of a database, we just need to revoke the permission
settings for the GUEST user. After that, only granted users are able to
access that database. To do so, we can refer to the following steps:

a. Click Start->All Programs->Microsoft SQL Server 2005-> SQL Server
Management Studio.

NOTE: By default the SQL Server Management Studio is not available in the
Start Menu. You can install this client tool from the following download
link manually:
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-
94b8-5a0f62bf7796&DisplayLang=en

b. Right click on the database reported by the MSBA tool -> select
Properties.
c. In the left panel, please click permission.
d. In the right panel, please check if the Guest account is in the "Users
or Roles". If so, please uncheck all the "Grant" checkbox if it is selected.
f. Use the similar method to revoke the permission for "Public" role if it
is also listed.
g. Click OK to apply the setting.

After that, it will remove the public access of the database in question.

Have a nice day!

Best regards,

Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Thread-Topic: How can I remove Public access to SQL Express db's?
| thread-index: Acfk9VpvbJnd5FVgRXCjRcnICVf84w==
| X-WBNR-Posting-Host: 207.46.192.207
| From: =?Utf-8?B?YnJhZA==?= <piraparana@xxxxxxxxxxxxxxxx>
| Subject: How can I remove Public access to SQL Express db's?
| Date: Wed, 22 Aug 2007 12:48:03 -0700
| Lines: 10
| Message-ID: <B6DE2AA4-53F0-452A-A4E6-9F785B251909@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.security
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.security:2242
| NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| I ran the MS Baseline Security analyzer on a PC with SQL Express
installed.
| The MBSA says "The following databases have public access.Remove the
public
| access if it is not required - "...
|
| How can I remove Public access to theseSQL Express db's? I don't see an
| option for that in the SQL Server Configuration Manager.
|
| -- |
| bb
|



.

Relevant Pages

  • Re: How can I remove Public access to SQL Express dbs?
    ... You can't delete guest but can render it unusable by revoking ... CONNECT permission in databases other than master and tempdb. ... it will remove the public access of the database in question. ...
    (microsoft.public.sqlserver.security)
  • Re: Windows Power User SQL
    ... The guest user must have connect permission in master and tempdb. ... When I run from the master database for example testing against user bill ...
    (microsoft.public.sqlserver.security)
  • Re: select from one db only
    ... Those databases are available to anyone who can login to the server because ... You should never remove guest from master ...
    (microsoft.public.sqlserver.security)
  • Re: Guest Account
    ... > The guest accout is a way for people to access a database - NOT the sever. ... > There is no login that is directly associated with guest... ... > As for the guest account in Northwind/Pubs - yes, ... > databases are sample databases - for learning. ...
    (microsoft.public.sqlserver.security)
  • Re: Moving SQL Objects
    ... Moving SQL Server databases to a new location with Detach/Attach ... Transfer Logins and Passwords Between Instances of SQL Server ... How to Resolve Permission Issues When a Database Is Moved Between SQL ...
    (microsoft.public.sqlserver.server)