Convert column data from Binary to String with SQL Server

declare @stringPassowrd varchar(max)

select @stringPassowrd = sys.fn_sqlvarbasetostr(UserPassword) from Users where UserID = 2

print @stringPassowrd

where UserPassword is the binary column you would like to convert.