Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Different query results for different users?

    I have an SQL2K5 database where a group of users share a custom database role and access the database via trusted connections (Windows authentication).

    I have a stored procedure which reports all of the users in this database role using an SQL statment something like this:

    select name
    from sysusers
    inner
    join sysmembers on
    sysmembers.memberuid=sysusers.uid
    and sysmembers.groupuid=7


    As the programmer, and the dbo, when I log in to execute this query, I get the list of a dozen, or so, users.

    When one of the users executes the same query, they only see a return of their own username.

    I'd like to have the users be able to execute this query and get the full list of users. Can someone tell me how to accomplish this?

    I assume that this is some security, or permissions, thing involving system tables, or Windows authentication security.

    Thank you.

    BTW--the users never actually directly execute the stored procedure I mentioned; They are using a VB client application that affects the execution.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Put the SELECT statement into a stored procedure instead of using it directly or in a view. I haven't tested this, but I'm 98% sure it will return all rows that way.

    -PatP

  3. #3
    Join Date
    Aug 2007
    Posts
    17
    could you alter the authentication config? try sqlserver authentication instead of Windows authentication.

    if it still can't work,please give some detail about the VB client application .

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Follow Pat's advice, and specify WITH EXECUTE AS OWNER clause, like this:

    create procedure dbo.myProc WITH EXECUTE AS OWNER AS
    select name
    from sysusers
    inner
    join sysmembers on
    sysmembers.memberuid=sysusers.uid
    and sysmembers.groupuid=7

    return (0)
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    WITH EXECUTE AS OWNER worked perfectly.

    Thanks all.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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