Problem: Login with admin rights

Question

I’m doing a login page using C# and my login works perfectly fine, one problem though is that I want to check if the person logged in has administrative rights or not so that I can direct them to a correct page. If for example Dorothy logged in I must direct her to an admin page. When I do my select statement I get different errors, can someone pls help with the correct SQL statement? I have three tables:

tblUser

UserId	Username	Password
1001	Dorothy	12345
1001	Jane	78965

tblRole

RoleId	RoleDescription
1111	Administrator
1112	Floor Manager
1113	Sales Director

tblUserRolelink

UserId	RoleId
1001	1111
1001	1113

Dorothy · Answer

Thanx guys for ur help, i finally got it right. i did some little modifying on fangza lro solution and it worked perfectly.

Kheen On Cheah · Answer

Yes, as long as the user exist it will always return back a record. You might want to look at the roleid/role description returned. If it's empty, prompt error or something, if it's specified then redirect to your specific page.

Dorothy · Answer

I had the similar select statement but it keeps on returning positive even if the user logged in doesn't have admin rights...Unless there's another method. Select r.RoleId, u.UserId, sys.RoleId, sys.UserId From Role r, User u, UserRoleLink sys Where r.RoleId = sys.roleId AND u.UserId = sys.UserId AND r.RoleDesc = 'Managing Director'

Kheen On Cheah · Answer

try select A.Username , C.RoleDescription from tblUser A , tblUserRolelink B , tblRole C where B.UserId = A.UserId and C.RoleId = B.RoleId and A.Username = xxxx ;

fangza lro · Answer

select u.username,r.roledescription from tblUser as u inner join tblUserRolelink as L on l.UserId=u.UserId inner join tblRole as r on r.RoleId=l.RoleId where u.Username=xxx and u.Password=xxx Is this SQL sentence OK?

Problem: Login with admin rights

Answers (5)