Post reply

Find Guest user access level in all databases

  • June 16, 2008 at 11:34 am

    #187113

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

    Maninder
    www.dbanation.com

  • November 29, 2022 at 7:58 pm

    #4119648

    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

     

  • November 29, 2022 at 10:33 pm

    #4119705

    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)

  • November 30, 2022 at 1:49 am

    #4119789

    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

  • November 30, 2022 at 1:53 am

    #4119790 

    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.

  • November 30, 2022 at 2:53 am

    #4119806

    @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)

  • November 30, 2022 at 2:59 am

    #4119807

    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