User View

  • I have 2 tables - a user table (TB_USERS) and a user customer table (TB_USER_CUST). I want to select all the users that share the same customer with the logged in user. I created a view that looks like this:

    SELECT user_id, fname, lname, password, phone, email

    FROM dbo.TB_USERS

    WHERE (user_id IN (SELECT user_id FROM dbo.TB_USER_CUST

    WHERE (cust_no IN (SELECT cust_no

    FROM dbo.TB_USER_CUST

    WHERE (user_id = USER))) OR (USER = 'dbo'))) AND (UPPER(domain) <> UPPER('SAMPLE'))

    This works fine. However, the DBA has just implemented groups. So now when a user logs in, they are part of a group and my view no longer works. The problem is the keyword 'USER'.

    Does anyone know how I can fox my view to take into account groups? If I use 'suser()' instead of 'USER', then it contains the domain name as well which won't works.

  • Rog Saber (5/29/2014)


    I have 2 tables - a user table (TB_USERS) and a user customer table (TB_USER_CUST). I want to select all the users that share the same customer with the logged in user. I created a view that looks like this:

    SELECT user_id, fname, lname, password, phone, email

    FROM dbo.TB_USERS

    WHERE (user_id IN (SELECT user_id FROM dbo.TB_USER_CUST

    WHERE (cust_no IN (SELECT cust_no

    FROM dbo.TB_USER_CUST

    WHERE (user_id = USER))) OR (USER = 'dbo'))) AND (UPPER(domain) <> UPPER('SAMPLE'))

    This works fine. However, the DBA has just implemented groups. So now when a user logs in, they are part of a group and my view no longer works. The problem is the keyword 'USER'.

    Does anyone know how I can fox my view to take into account groups? If I use 'suser()' instead of 'USER', then it contains the domain name as well which won't works.

    Not exactly sure what your question is here. Are you trying to find if a user belongs to a group?

    But why in the world are you storing passwords in plain text? Passwords should be encrypted.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I guess I need a definition of a 'user' and a definition for a 'customer' for you are asking us to understand the business logic to see if your query is correct.

    If you need a user name without the domain portion a little string manipulation is all that's called for >

    I just quickly came up with

    select right(system_user,len(system_user)-charindex('\',system_user,1) )

    If this piece doesn't help at least I hope it gives you an idea.

    ----------------------------------------------------

Viewing 3 posts - 1 through 2 (of 2 total)

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