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
订阅:
博文评论 (Atom)
没有评论:
发表评论