May 29, 2014 at 12:06 pm
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.
May 29, 2014 at 12:14 pm
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/
July 25, 2014 at 6:13 pm
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