2012年2月25日星期六

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

没有评论:

发表评论