April 28, 2011 at 8:41 am
Don't know if anyone here can help but thought I would try anyway.
I have an application that I want to convert to a Web app. The Web needs a common ID to log in which works just fine. However, I still need to know the user ID of who is loggin in. So, to do this, I will need a login window in my app where I will capture the user ID of whomever is logging in - I can save this as a variable.
The problem I have is that I have a bunch views. These views are from tables that have a user_id field that I am joining with USER in the SQL statement for the view. The USER in the case is the generic
log on used to connect to the DB in the WEB - NOT the user that logged in to the app. Any ideas how to get around this?
For example, here is one of my views:
SELECT cust_no, cust_name
FROM dbo.RTO_CUSTOMER
WHERE (cust_no IN
(SELECT cust_no
FROM dbo.RTO_USER_CUST
WHERE (user_id = USER)))
I need the where clause to be where user_id = logging in user
April 28, 2011 at 9:03 am
This is probably the wrong forum for this question. Although it may sound like a general 2008 question, it is at least TSQL related, and more likely related to front-end dev.
Basically, in your situation, you have 2 different users that you are working with; the app user (taken from your web app login box and looking at a SQL Server user table to select the userid from their provided username and password), and a totally separate SQL server login, which the web server will need to use to authenticate to the SQL box to run the above command in the first place.
Your database views sound like they expect the where clause userid to come from a users table, and not from the SQL login (or the users listed in the security tab of the database).
This is just one design by the way. you can configure things for the web app to use the users windows username to authenticate to SQL, as well as many other plans. Really, the options fill complete books.
If you go with the top method which I think you are trying to implement, you save the userid from the users table into the web apps SESSION so other pages can read thst session to pull the userid and feed them to the SQL statement to filter that page.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 28, 2011 at 9:05 am
OK - I will move it.
Thanks
April 28, 2011 at 9:13 am
Jim has some good advice here. The main problem is trying to track the web user. If you move to requiring the person accessing the web site to log in, note that this will limit the people that can use the web site to those that are in your AD domain. That isn't necessarily a problem, but something you need to be aware of.
In IIS, you can configure an anonymous login (not what you want), or a prompt for the Windows user/pwd, which can pass through.
You can also just allow the database access call to pop a user/pwd dialog, but that would mean you need to define SQL logins, which is not necessarily what you want to do.
Actually, let me ask now. How do users log into the SQL Server now for their access? Windows credentials or user/pwd from SQL Server? Or something else?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 28, 2011 at 9:33 am
No further replies please. Thread continues http://qa.sqlservercentral.com/Forums/Topic1100316-392-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply