I need the logged in user to be able to see other users based on his user role. If he is administrator he should see all, if he is superuser he should see other super users and user, if he is user he should only see other users. The below query returns only rows with the same user role, but not with a "lower" user role.
Can someone point be in the right direction?
Thanks for any help.
DECLARE @UserName as varchar(59)
SET @UserName ='mb'
SELECT [UserID], [UserName], [FullName], [Password], [UserAccessID], [CompanyID], [UserRole]
WHERE UserRole =
CASE WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Administrator') THEN
WHEN EXISTS (SELECT * FROM [Users] WHERE [UserName] = @username and [UserRole] ='Superuser') THEN
ORDER BY [UserName] ASC