Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: Building Locks by Guest user

    Hi,

    We have had instances where database is standstill due to building locks by user named 'guest'.

    I undestand that If a user
    issues the use database_name command, and his or her name is not found
    in the database’s sysusers or sysalternates table, Adaptive Server looks for
    a guest user. If there is one, the user is allowed to access the database, with
    the permissions of the guest user.

    1. How do i find out who is the user who is actually using 'guest' id on other databases like this
    2. Guest by default is in 'public' group. Is it ok to remove guest from public group ?
    3.the last time i saw the locks, the application reported was 'microsoft access'....is there a way to control the no.of ODBC connections to a datbase ?

    Thanks for help.
    Rav.

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    12
    1. If you just run sp_who it shows you the loginame used to log onto the server. If it shows guest then check it they are actually logging into the server with an account called guest.
    2. All users are members of public and can't be removed, you can only add them to another group.
    3. In v 12.0 and up, you can add resource limits that can be dependent on the application name - in this case Ms Access. I am not sure whether it allows limiting connections, have a look through the manual.
    Will

  3. #3
    Join Date
    Nov 2003
    Posts
    10
    Originally posted by willl
    1. If you just run sp_who it shows you the loginame used to log onto the server. If it shows guest then check it they are actually logging into the server with an account called guest.
    2. All users are members of public and can't be removed, you can only add them to another group.
    3. In v 12.0 and up, you can add resource limits that can be dependent on the application name - in this case Ms Access. I am not sure whether it allows limiting connections, have a look through the manual.
    Hi,

    Thanks for the reply.

    1. if i run sp_who it shows 'loginname' as guest, but the 'hostname' is blank.
    Does this mean that someone logged onto the server as user 'guest'
    or does it indicate that some user with valid login has done 'use <dbname> ' and hence assumed guest login for that database ?

    Any ideas on how to avoid building locks, especially by guest user using microsoft access ?

    Rgds,

  4. #4
    Join Date
    Sep 2003
    Location
    London
    Posts
    12
    I would have thought it meant they logged on as guest, but I could be wrong. Try "sp_displaylogin guest" to check if there is a guest login or not.

    I dont' have experience with Access, I imagine the app leaves transactions open, perhaps the people who wrote the application can help...
    Will

  5. #5
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    > 1. How do i find out who is the user who is actually using 'guest' id on other databases like this

    Try this select in the databases with "guest users", and see the "user_name" :

    USE yourDB
    go
    select suser_name(suid) login_name,
    user_name(uid) user_name,
    user_name(gid) group_name,
    hostname,
    program_name,
    hostprocess,
    spid,
    status,
    cmd,
    physical_io,
    cpu,
    memusage,
    blocked,
    time_blocked,
    tran_name,
    priority
    from master..sysprocesses
    where dbid = db_id()
    and suid <> 0
    order by db_name,
    user_name,
    group_name
    go

    > 2. Guest by default is in 'public' group. Is it ok to remove guest from public group ?

    If you drop the "guest" user then just the user that exist in this databases will connect (for security is better).

    > 3. the last time i saw the locks, the application reported was 'microsoft access'....is there a way to control the no.of ODBC connections to a datbase ?

    May be the login triggers will help "http://www.sypron.nl/logtrig.html"

    bye bye

    Sebastian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •