Obviamente, esa instrucción es para MySQL
Para MS SQL puedes convertir el tipo de string y comparar ambas cadena:
set nocount on
select * from usuarios
where
CONVERT(VARBINARY(30), rtrim(login)) = CONVERT(VARBINARY(30), rtrim(@login))
Aunque si el campo login no es de tipo VARBINARY te recomiendo que uses strComp de ASP como te he puesto antes.
Un saludo