Cita: Siempre o es 1 o es 0 sin importar el ususario.
No veo el error, es lo que estás diciendo que haga.
En cualquier caso, RETURN se suele utilizar para mensajes de error. Si quieres regresar valores prueba las variables de retorno:
CREATE PROCEDURE ValidaUsuario
@Usuario nvarchar(50) ,
@Password nvarchar(50),
@tiene_acceso bit OUTPUT
AS
if (SELECT Top 1 Usuario From dbo.Usuarios WHERE usuario = usuario) is null
set @tiene_acceso = 0
else
set @tiene_acceso = 1
GO