Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1< 2 0 guest 0x00 0x00 2000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!'!?!
Any help greatly appreciated, thank you
TimGuest always exists in sysusers, it's just only enabled when
you add the user. If it's not enabled in the database, the
status is 0 in sysusers.
-Sue
On 6 Apr 2004 09:07:17 -0700, google@.supertimmy.com (Tim
Gorgs) wrote:

>Hi all, I want to disable access of the SQL guest account except
>master and tempdb. I tried it for one database now, but before I used
>this script in query analyzer to check whether the guest account had
>access whatsoever:
>select has_dbaccess('DatabaseName') as Access,* from sysusers
>and it gave me a 1 for the row corresponding to guest:
>now I tried to disable it for the database via:
>USE <DatabaseName
>GO
>EXECUTE sp_revokedbaccess guest
>and I got an appropriate message that the account was disabled.
>When I queried the sysusers table again as I did in the first
>statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
>this possible? In addition to this problem I tried to disable it again
>for the same database, but now I get "User 'guest' does not exist in
>the current database." which is actually that what I expected after
>disabling the account, but why does it still show up in sysusers with
>access to exactly the database that I disabled its access to!'!?!
>Any help greatly appreciated, thank you
>Tim|||> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.com...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!'!?!
> Any help greatly appreciated, thank you
> Tim|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim

Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1<20guest0x000x002000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!?!?!
Any help greatly appreciated, thank you
Tim
> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.c om...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!?!?!
> Any help greatly appreciated, thank you
> Tim
|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim

disable guest account in MSDB

I'm trying to enforce DTS package managment security in an
Enterprise Enviroment. Microsoft has published KB Article 282463
related, recommending disable guest account in msdb. But,
what it's the impact of doing that? it's just a simple
"execute sp_revokedbaccess 'guest'" ' will everything continue
functioning normally?
Thanks
--
Javier Herrera P.
MCDBA - OCP
SQL*Technology
Santiago - Chile
--Generally things would be okay but it depends on if you have
anything setup or configured that relies on users gaining
access to msdb through the guest account. You'd need to
assess that based on your databases, apps, etc. Removing
guest from msdb means that users would need be granted
access to msdb in order to execute or access anything that
the guest account access through permissions via the public
role.
-Sue
On Mon, 2 Aug 2004 06:55:05 -0700, "Javier Herrera P."
<Javier Herrera P.@.discussions.microsoft.com> wrote:

>I'm trying to enforce DTS package managment security in an
>Enterprise Enviroment. Microsoft has published KB Article 282463
>related, recommending disable guest account in msdb. But,
>what it's the impact of doing that? it's just a simple
>"execute sp_revokedbaccess 'guest'" ' will everything continue
>functioning normally?
>Thanks
>--
>Javier Herrera P.
>MCDBA - OCP
>SQL*Technology
>Santiago - Chile
>--