You should write like this because of Lexical scoping like a fox, Variable Scoping in Perl: the basics

sub sa_details {
   my $dbh = shift;
   my @uids;
   
   $dbh->dbcmd("SELECT userid FROM user");
   $dbh->dbsqlexec or die "Can't execute SQL statement:\n";
   
   while( $dbh->dbresults != NO_MORE_RESULTS ){
       while( my @row = $dbh->dbnextrow ){
           push @uids, $row[0];
       }
   }
   return @uids;
}

my @array = sa_details(
    Sybase::DBlib->new(
        "user",
        "passwd",
        "host",
    )
);
[download]

Or you could write

sub sa_details {
    my $dbh = shift;
    $dbh->nsql( "SELECT userid FROM user", "ARRAY" );
}
my @arry = sa_details(
    Sybase::DBlib->new(
        "user",
        "passwd",
        "host",
    )
);

$DB_ERROR and die $DB_ERROR;
## $Sybase::DBlib::DB_ERROR and die $Sybase::DBlib::DB_ERROR;
[download]

Is there a reason you are using Sybase::DBlib instead of the more usual DBI?

DBI has a handy selectcol_arrayref() function which folds a single-column resultset into an array reference.

sub sa_details {
  my $dbh = ...

  return $dbh->selectcol_arrayref(
    qq{SELECT userid FROM user}
  );
}

my $arrayref = sa_details();
[download]

(Usually you use DBI with the prepare-execute-fetch combination, though. The select{row,all,col} functions are there just for convenience.)