Find Guest user access level in all databases

  • Comments posted to this topic are about the item Find Guest user access level in all databases

  • Manny:-

    I do not think example 2 actually works.

    Here is your SQL Statement for Example 2:-

    Declare @holdname sysname
    Declare getdbname cursor for
    Select name from master.sys.databases order by name
    Open getdbname
    fetch next from getdbname into @holdname
    while @@fetch_status=0
    BEGIN
        select @holdname,name,hasdbaccess from sysusers where name like 'guest%'
    fetch next from getdbname into @holdname
    END
    close getdbname
    deallocate getdbname

     

    You are accessing the same database over and over again within the loop.

    That is the contextual/current database.

    Nevertheless, Thanks for sharing.

    It is easy to try things out and determine whether there are available choices towards remediation.

    Daniel

     

  • Daniel Adeniji-180202 wrote:

    Manny:-

    I do not think example 2 actually works.

    Here is your SQL Statement for Example 2:-

    Declare @holdname sysname
    Declare getdbname cursor for
    Select name from master.sys.databases order by name
    Open getdbname
    fetch next from getdbname into @holdname
    while @@fetch_status=0
    BEGIN
        select @holdname,name,hasdbaccess from sysusers where name like 'guest%'
    fetch next from getdbname into @holdname
    END
    close getdbname
    deallocate getdbname

    You are accessing the same database over and over again within the loop.

    That is the contextual/current database.

    Nevertheless, Thanks for sharing.

    It is easy to try things out and determine whether there are available choices towards remediation.

    Daniel

    Folks can prove what Daniel is saying by adding the DB_NAME() function to the select as follows...

        select @holdname,name,hasdbaccess,DB_NAME() from sysusers where name like 'guest%'

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden:-

    Yes, that was my exact test.

    I have attached a test sql code.

    Jeff, thanks for being a real one.

    Sacrificing your soul to make sure that everyone gets a lot more.

    Amen,

    Daniel

  • Declare @holdname sysname

    Declare getdbname cursor for
    Select name
    from master.sys.databases
    order by name

    Open getdbname

    fetch next
    from getdbname
    into @holdname

    while @@fetch_status=0
    BEGIN

    select
    [holdname] = @holdname
    , [userid] = [uid]
    , [username] = [name]
    , [database] = db_name()
    , hasdbaccess = hasdbaccess

    from sysusers

    where (
    ( [name] = 'guest' )

    or ( [uid] = 2 )

    )

    fetch next
    from getdbname
    into @holdname

    END

    close getdbname
    deallocate getdbname
    Attachments:
    You must be logged in to view attached files.
  • @MannySingh... despite what was found on script 2, thanks for stepping up to the plate to teach.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Daniel Adeniji-180202 wrote:

    Jeff Moden:-

    Yes, that was my exact test.

    I have attached a test sql code.

    Jeff, thanks for being a real one.

    Sacrificing your soul to make sure that everyone gets a lot more.

    Amen,

    Daniel

    Thank you for the kind words, Daniel, but it was no sacrifice on my part.  You made a correct observation and I just provided a simple way for others to confirm it if they didn't understand it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply